Thursday, October 29, 2009

SQL Server Database analysis scripts







While browsing the net my team came across couple of very good analytical scripts, i would like to share this with everyone. often there is a scenario where you wonder how to find out what script or procedure is consuming most memory or have the most reads, this help to identify the possible bottle necks in the website.

The query below is really useful

1) Following Query will return top 10 queries having maximum order by number of executions:




SELECT TOP 10 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = 'DatabaseName')
ORDER BY qs.total_physical_reads DESC
Though this is not as useful as query one, but can be handy in case of really large databases, where you need to identify what table is consuming how much memory, and may be the the data in some tables is not all that relevant now and can be deleted conditionally, help improve the performance.

2) Query to check the size of each table:



EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
One major point, you need admin/sa access to run these scripts
Look forward to your comments on the same



kick it on DotNetKicks.com