T-SQL Tuesday#17: It’s all about APPLYcation this time

imageIt’s time for another round of T-SQL Tuesday and this round of the revolving blog party is being hosted by Matt Velic [Blog | Twitter].

APPLY – That is the topic for this month’s T-SQL Tuesday! The APPLY operator was added to the T-SQL repertoire and which has resulted in lesser use of cursors for a large number of diagnostic scripts that CSS uses to collect data while working on SQL Performance issues. In this blog, I shall share a few examples of such queries that we use to collect data while working on SQL Performance cases.

TOP Query Plan Statistics

The following query gives you a list of the SQL batches/procedures with their CPU usage, Query/Batch duration and Physical Reads rank. This query helps identify the TOP CPU/Duration/Read consuming queries by making use of system DMVs. The output below is useful for the following reasons:

1. I get the usecount of the procedure/batch and if this batch is called multiple times and the use count of a Compiled Proc cached object is only 1, then the plan is not being re-used. This now tells me that I need to look at reasons behind inability of plan re-use.

2. I get the total and average resource usage statistics for each of the queries listed in the output.

3. A quick glance at the output gives me an idea of the most expensive queries on the instance w.r.t. reads or/and CPU and/or query duration.


SELECT

LEFT(p.cacheobjtype + ' (' + p.objtype + ')',35) AS cacheobjtype,

p.usecounts,

p.size_in_bytes/1024  AS size_in_kb,

PlanStats.total_worker_time/1000 AS tot_cpu_ms,

PlanStats.total_elapsed_time/1000 AS tot_duration_ms,

PlanStats.total_physical_reads,

PlanStats.total_logical_writes,

PlanStats.total_logical_reads,

PlanStats.CpuRank,

PlanStats.PhysicalReadsRank,

PlanStats.DurationRank,

LEFT(CASE WHEN pa.value = 32767 THEN 'ResourceDb' ELSE ISNULL(DB_NAME(CONVERT(sysname,pa.value)),CONVERT(sysname,pa.value)) END,40) AS dbname,

sql.objectid,

CONVERT(nvarchar(50), CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE(REPLACE(sql.[text],CHAR(13),' '),CHAR(10),' ') END) AS procname,  REPLACE(REPLACE(SUBSTRING(sql.[text],PlanStats.statement_start_offset/2+1,CASE WHEN PlanStats.statement_end_offset=-1 THEN LEN(CONVERT(nvarchar(max),sql.[text]))

ELSE PlanStats.statement_end_offset/2 - PlanStats.statement_start_offset/2+1 END),CHAR(13),' '),CHAR(10),' ') AS stmt_text

FROM

(

SELECT

stat.plan_handle,

statement_start_offset,

statement_end_offset,

stat.total_worker_time,

stat.total_elapsed_time,

stat.total_physical_reads,

stat.total_logical_writes,

stat.total_logical_reads,

ROW_NUMBER()OVER ( ORDER BY stat.total_worker_time DESC ) AS CpuRank,

ROW_NUMBER()OVER ( ORDER BY stat.total_physical_reads DESC ) AS PhysicalReadsRank,

ROW_NUMBER()OVER ( ORDER BY stat.total_elapsed_time DESC ) AS DurationRank

FROM sys.dm_exec_query_stats stat

) AS PlanStats

INNER JOIN sys.dm_exec_cached_plans p

ON p.plan_handle =  PlanStats.plan_handle

OUTER APPLY sys.dm_exec_plan_attributes ( p.plan_handle ) pa

OUTER APPLY sys.dm_exec_sql_text ( p.plan_handle ) AS sql

WHERE

(PlanStats.CpuRank<50

OR PlanStats.PhysicalReadsRank<50

OR PlanStats.DurationRank<50)

AND

pa.attribute='dbid'

ORDER BY tot_cpu_ms DESC


Top Queries with Similar Query Hash and Query Plan Hash

 

SELECT TOP 10 query_plan_hash, query_hash,

COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',

SUM(execution_count) as 'execution_count',

SUM(total_worker_time) as 'total_worker_time',

SUM(total_elapsed_time) as 'total_elapsed_time',

SUM (total_logical_reads) as 'total_logical_reads',

MAX(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1 END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text,

MIN(CAST(query_plan as varchar(max))) AS 'ShowPlan XML'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as sp

GROUP BY query_plan_hash, query_hash

ORDER BY sum(total_worker_time) ASC;

This is a query which can help you identify queries which have the same query plan hash

SQL Server Books Online topic “Finding and Tuning Similar Queries by Using Query and Query Plan Hashes” has more information on this topic. The query hash feature was added in SQL Server 2008 which made it easier to troubleshooting performance issues caused by ad-hoc queries which differed in just literal values. RML Utilities does a similar task by creating query hash but now if you are troubleshooting on the server, you can do this using DMVs without having to capture a profiler trace.

The right operand supplied to the Apply operator is a function of one or more column values that are present in the left operand. So basically, the right operand is a table-valued expression of which is evaluated once for each row that appears in the left operand. The Cross Apply and Outer Apply are the two flavors of the Apply operator. So if I wanted to simulate an Apply Operation without the Operator itself, it would require the use of temporary tables or table variables.

I use the APPLY operator a lot while parsing XML data like Process Monitor traces or XML query plans which make life a lot easier and saves me from writing a huge bunch of T-SQL code.

How to get File Space used information

There are multiple ways to get this done. Starting from SQL Server 2005, you can get this information using the system logs to get the similar kind of information using system catalogs.

This can be achieved using sys.master_files and FileProperty function. The query below will give you the same information as you see in the Object Explorer window when you do the following: Right Click database name –> Tasks –> Shrink –> Files.

EXEC sp_MSforeachdb 'SELECT DB_NAME() as database_name,
name as [File Name],
physical_name as [Physical Name],
size/128.0 as [Total Size in MB],
size/128.0 - CAST (FILEPROPERTY(name,''SpaceUsed'') as int)/128.0 AS [Available Space in MB]. [file_id]
FROM sys.database_files;'

How fetch sysaltfiles information using Powershell

I recently have developed an affinity for using Powershell. I saw a question on #sqlhelp hashtag for fetching database properties using Powershell. There are multiple posts out there on the web to do this using SMO. A crude way would be to use the Invoke-Sqlcmd cmdlet to do this.

Command:

Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"

If you wanted a cleaner output or some post processing done on the results fetched and wanted to use foreach, them this could also be done:

Example:

$dbprop = Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"
foreach ($db in $dbprop)
{
Write-Host $db.filename
}

If you are using SQLPS, then the above command to give the information that you want by invoking SQLCMD using Powershell.

Other ways to do this are mentioned here:

Get SQL database size using Windows Powershell

Get database properties using PowerShell in SQL Server 2008 by Tim Chapman (Blog)

How to find out BINN folder path using WMI

A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:


strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")

Set colItems = objWMIService.ExecQuery( _

"SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)

For Each objItem in colItems

Wscript.Echo "-----------------------------------"

Wscript.Echo "SqlService instance"

Wscript.Echo "-----------------------------------"

Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)

Next

The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.

For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.

The same is possible through the much talked about Powershell as well.

Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.

Thanks to @afernandez for pointing out another way through a CLR TVF.

Generating SELECT INTO scripts for all user tables

Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:

1. Export/Import Wizard

2. Custom SSIS Task

3. Copy Database Wizard

4. BCP operations

5. SELECT INTO T-SQL commands

I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:

 select 'select * into <new db>.'+b.name+'.['+a.name+'] from <old db>.'+b.name+'.['+a.name+']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U' 

Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.

How to find out if a table exists on the instance

 Once in a while I find that need to find out if a particular table name exists in any of the databases of my SQL Server instance. This is when sp_MSforeachdb comes into play.

EXEC sp_MSforeachdb '
if exists(select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0)
begin
    print ''Database Name: '' + ''?''
    select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0
end'

The above piece of T-SQL code finds out if there exists a user table which has a string “log” in it’s name on all the databases of the instance. You can keep modifying this query by adding more filters in the WHERE clause. Current filters only looks for tables which are not shipped by Microsoft (Eg. user tables created by replication, database tuning advisor, log shipping etc.). Basically any user table created by you. If you want to use this on a SQL Server 2000 instance, then you would need to use sysobjects instead of sys.objects.

Steps to script out the primary key constraints or clustered indexes in a database using DMO

Why do you need this? One of the drawbacks of the “Generate Scripts” option in SQL Server Management Studio is that SSMS scripts out the Primary Key constraints along with the CREATE TABLE script. So, if the table is already created, the primary key will not be created if you use the script that was generated by the SSMS Generate Scripts Wizard

The steps that you need to follow are for generating the primary key constraints or clustered indexes for user defined tables: 

1. You will have to create the Stored Procedure from my previous blog post on the source database. 

2. Then enable the OLE AUTOMATION and XP_CMDSHELL on the server. 

EXEC sp_configure 'Show Advanced',1

reconfigure with override

go

EXEC sp_configure 'Ole Automation',1

reconfigure with override

go

EXEC sp_configure 'xp_cmdshell',1

reconfigure with override

go

 

3. Use the following script to generate the list of Primary Keys for user defined tables in a database: 

Script for generating primary key creation script: 

set nocount on 

print 'DECLARE @return_value int ' 

select ' 

EXEC @return_value = [dbo].[proc_genscript] 

@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/ 

@DBName = ''Northwind'', /*Replace this with the database name*/ 

@ObjectName = '''+[name]+''', 

@ObjectType = ''KEY'', 

@TableName = ''' +object_name(parent_object_id)+''', 

@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */ 

SELECT ''Return Value '' = @return_value'+char(13) 

from sys.objects 

where type = 'PK' 

and parent_object_id in (select id from sys.sysobjects where xtype = 'U')

Script for generating clustered indexes creation script: 

set nocount on 

print 'DECLARE @return_value int ' 

select ' 

EXEC @return_value = [dbo].[proc_genscript] 

@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/ 

@DBName = ''Northwind'', /*Replace this with the database name*/ 

@ObjectName = '''+[name]+''', 

@ObjectType = ''INDEX'', @TableName = ''' +object_name(id)+''', 

@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */ 

SELECT ''Return Value '' = @return_value'+char(13) 

from sys.objects 

where type = 'PK' 

and parent_object_id in (select id from sys.sysobjects where xtype = 'U')

4. Then use the scripts obtained from the above output to generate the Primary Key creation scripts in the folder that you mentioned. In the above example, the *.sql scripts would get created in the C:\Database folder. 

5. Then use the following script to generate the SQLCMD commands for running those *.sql scripts against the source database. 

create table #filenames (file_name varchar(1000))

insert into #filenames

exec xp_cmdshell 'dir <folder path>\*.sql /b'

select 'sqlcmd -S <servername> -d <databasename> -E -i ' +file_name

from #filenames

where file_name like '%sql%'

drop table #filenames

where  

<server name> = SQL Server instance name 

<database name> = Database Name on which you want the objects to be created 

<folder path> = the folder where you want the .sql file to be created. This has to be the same folder path where you saved the .sql files in Step 3. 

6. Now put the commands obtained in Step 5 into a .bat file saved at the same location where the .sql files were created in Step 5. Run the .BAT file from a command prompt window and all the script files will get executed against the context of the database that you provided. 

The above set of steps loops through the sysindexes system catalog and picks up all non-fulltext index and generates the CREATE INDEX scripts for all the user database tables using DMO. 

**** Before running this on a production database, please test out the above solution on a test database