Query Store Catalog Views – Part 6

Disclaimer :  Subject to change with new CTP updates. FROM BOL: The Query Store can be viewed and managed through Management Studio or by using the following views and procedures. sys.fn_stmt_sql_handle_from_sql_stmt Query Store Catalog Views Gives detail information about the Query Store. sys.database_query_store_options sys.query_context_settings sys.query_store_plan sys.query_store_query sys.query_store_query_text sys.query_store_runtime_stats sys.query_store_runtime_stats_interval Query Store Stored Procedures Stored procedures […]

Query Store – Force Plan – Part 5

Maintaining Query Performance Stability For queries that are executed multiple times you may notice that SQL Server used different plans which resulted in different resource utilization and duration. With Query Store you can easily detect when the query performance regressed and determine the optimal plan within a period of interest. Then you can force that […]

Query Store – Plans Usage – Part 4

In Regressed Queries , The Plan summary displays information on how many query plan were generated for a query and when those plans were used.   On mouse over on the bubble , it will display the below information   The Grid view will display the graphical representational data

Query Store – Part 2

Part –1 is on how to enable Query Store, in this post we will see what happens once Query Store is enabled. Once the Query Store is enabled, 4 Query monitor are configured which is under Query Store folder   Regressed Queries : The Regressed Queries pane shows you the queries, and plans in the […]

Query Store – Part 1

[BOL] The SQL Server Query Store feature provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates […]

Copy backup file between different domains using mapped network drives

How to copy backup files between to different domains having trust relationship? use master go exec xp_cmdshell ‘net use Z: \sourceserverH$Backups password /user:domainusername’ go exec usp_copybackupfiles @searchdirectory=’Z:’, @filefilter=’.bak’, @database=’AdventureWorks2014′, @Destination=’H:Backupscopyfiles’ go Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ go  

Copy backup files between locations –TSQL

Handy Script to Copy the latest backup file between locations /* Written by : Mohammad Sufian Location : Singapore Copywrite : None. Suggestion/Feedback : gosgenq@gmail.com Supported versions: SQL Server 2008 R2 to SQL Server 2014 */ CREATE PROCEDURE dbo.usp_copybackupfiles @searchdirectory nvarchar(4000), @filefilter varchar(7), @database varchar(128), @Destination nvarchar(4000) AS BEGIN SET NOCOUNT ON DECLARE @command varchar(8000) […]

Map – Disconnect network drives – TSQL

Configure Map Network Drive using T-SQL GO xp_cmdshell “net use Z: \\ServerName\drive$\FolderName <password> /user:domainusername GO Disconnect Mapped Network drives GO Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ GO

Resource Governor and In-Memory OLTP

From BOL A resource pool represents a subset of physical resources that can be governed. By default, SQL Server databases are bound to and consume the resources of the default resource pool. To protect SQL Server from having its resources consumed by one or more memory-optimized tables, and to prevent other memory users from consuming […]

xtp Transaction stuck

Why the xtp transaction do not kill or rollback when a long running process is stopped? A long running transaction inserting millions of records in a In-Memory OLTP Table  is stopped but the spid remains in running state, it do not kill or rollback. Even you try to kill the session it do not actually […]

BUCKET_COUNT for Hash Indexes for SQL Server Memory Optimized Tables

  Indexes are used as entry points for memory-optimized tables.Data in memory is quickly read by index to locate in-memory data. [From BOL] A hash index consists of a collection of buckets organized in an array. A hash function maps index keys to corresponding buckets in the hash index. The following figure shows three index […]

Monitor AlwaysOn Log QueueRate and Redo Queue

Monitoring alwayson Log Send Queue and Redo Queue DMV’s sys.dm_hadr_database_replica_states & sys.availability_replicas provided the information on log send queue and redo Queue size SELECT DB_NAME(database_id) AS dbname,ar.replica_server_name, CASE drs.is_local WHEN 0 THEN ‘REMOTE’ ELSE ‘LOCAL’ END AS is_local, drs.last_hardened_time, drs.log_send_queue_size / 1024 AS MB_log_SendQueueSize, drs.log_send_rate / 1024 AS MB_log_send_rate, drs.redo_queue_size / 1024 AS MB_redo_queue_size, drs.redo_rate / 1024 […]

Timeout with Multi-subnet Listener

Time-out error and you cannot connect to a SQL Server 2012 AlwaysOn availability group listener in a multi-subnet environment Once you are done with AG Creation and Listener configuration , change the RegisterAllProvidersIP value from 1 to 0. PS C:Users> Get-ClusterResource AG_ListenerName| Get-ClusterParameter RegisterAllProvidersIP Change to PS C:Users> Get-ClusterResource AG_ListenerName | Set-ClusterParameter RegisterAllProvidersIP 0