Author Archives: Ken

SQL Server 6.5 Move Master/​SystemDB Location

Microsoft has an extra sp_​​movedevice stored pro­ced­ure to update sys­devices table and then you need use Enter­prise man­ager to update registry info. Relo­cate the SQL 6.5 Master.dat file to a dir­ect­ory path that does not have spaces. To do this, per­form the fol­low­ing steps: If SQL Serv­er 7.0 is on the same com­puter as SQL 6.5, ver­sion switch […]

MSSQL DMVs — Tempdb Related

The total num­ber of free pages and total free space in mega­bytes (MB) avail­able in all files in tem­p­db SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; The Amount Space Used by the Ver­sion Store SELECT SUM(version_store_reserved_page_count) AS [ver­sion store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [ver­sion store space in MB] FROM sys.dm_db_file_space_usage; […]

MSSQL DMVS — Transaction Locks

Some­times we wish to know what locks are being held by the trans­ac­tion. Also it would be great to know what SPID owns the trans­ac­tion, on which objects the locks are being held, what SQL state­ment caused the locks, etc… SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS Data­base­Name, O.Name AS Locke­dOb­ject­Name, P.object_id AS Locke­dOb­ject­Id, L.resource_type AS […]

Partitioning — Two Queries

Query 1: Find exist­ing par­ti­tions details in seconds select dis­tinct p.object_id, index_​name =, index_​type_​desc = i.type_desc, partition_​scheme = ps​.name, data_​space_​id = ps.data_space_id, function_​name = pf​.name, function_​id = ps.function_id from sys.partitions p inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id inner join sys.partition_schemes […]

MSSQL DMVs — SQL Statements Text

It is always good to see what was the last query of any ses­sions. SELECT cs.session_id, cs.connect_time, cs.last_read, cs.last_write, sp.loginame, sp.hostname, TEXT FROM sys.dm_exec_connections cs INNER JOIN sys.sysprocesses sp ON cs.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(cs.most_recent_sql_handle) AS ST where sp.dbid = db_id(‘DB’) The fol­low­ing query will tell you what’s the run­ning at the moment. SELECT […]

MSSQL DMVs — Worst Performing Queries

Fol­low­ing query will return TOP 100 worst per­form­ing SQL State­ment with the name of object that con­tain those state­ments e.g. Stored Pro­ced­ure, Trig­ger and Func­tion. Cur­rent data­base con­text will be used so change the data­base before execut­ing query. The res­ult will be sor­ted by CPU time in des­cend­ing order. SELECT TOP 100 [Object_​Name] = object_name(st.objectid), creation_time, […]

Bible — SQL Server Trace Flags Lookup

Here is the com­pre­hens­ive list of most trace­flags known. 🙂 flag Trace Flag Descrip­tion (under­lined are sp_configure’able) ‑1 Sets trace flags for all con­nec­tions. Used only with DBCC TRACEON and TRACEOFF. The set­ting of the Trace flag ‑1 is not vis­ible with DBCC TRACESTATUS com­mand, but work without prob­lems. 105 SQL Serv­er 6.5 you can use maximum […]

MSSQL DMVs — Backup Progress Status Report

In SQL2000, we can­’t find out the backup pro­gress unless we give the stats para­met­ers in the backup state­ment. But in SQL 2005/​​2008, with DMVs we are cap­able of find out and now we can answer most of user ques­tions like “When the backup will fin­ish? How far we’ve gone through the whole backup?”. SELECT […]

Identifying Overlapping Statistics

Hav­ing both auto cre­ated stat­ist­ics and index stat­ist­ics on the same column caused the query optim­izer to choose a dif­fer­ent — and less than optim­al — exe­cu­tion plan than when only the index stat­ist­ics exis­ted. From Author : Kend­al Van Dyke http://​sqlserver​pedia​.com/​b​l​o​g​/​s​q​l​—​s​e​r​v​e​r​—​2​0​0​5​/​t​u​n​i​n​g​—​t​i​p​—​i​d​e​n​t​i​f​y​i​n​g​—​o​v​e​r​l​a​p​p​i​n​g​—​s​t​a​t​i​s​t​i​cs/ WITH auto­stats ( object_​id, stats_​id, name, column_​id ) AS ( SELECT sys.stats.object_id , sys.stats.stats_id […]

MSSQL DMVs — CPU Related

Here are a few quer­ies that I’ve col­lec­ted that can be used to find out any CPU related issues. 1. Top 20 HIGH CPU batches. SELECT TOP 20 (total_​worker_​time * 1.0) /​ 1000000 AS CPU_​Utilized_​in_​Seconds, convert(varchar(255), text) as TEXT FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) order by (total_​worker_​time * 1.0) /​ 1000000 desc 2. Top 20 […]