IT Giant

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 […]

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; […]

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 […]

Query 1: Find exist­ing par­ti­tions details in seconds select dis­tinct p.object_id, index_​name = i.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 […]

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 […]

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, […]

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 […]

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 […]