The SQL Dude!

Pages that teach you the SQL way of life!

Archive for the ‘T-SQL’ Category

T-SQL Script to find the size of every index in the database

Posted by Sudarshan Narasimhan on December 15, 2012


I wrote this query to find the size of all indexes in the database for one of my customers. Thought I’d share this script with everyone. Cheers.

CREATE PROCEDURE uspIndexSizeinDB
AS 
-- Author: Sudarshan (TheSQLDude.com)
-- Data Created: Dec 15, 2012
-- Description: Calculate the SQL Server index size for all indexes in the current database
  BEGIN 
    DECLARE  @IndexSize BIGINT, 
             @IndexID   INT,
             @IndexName nvarchar(200),
             @IndexType nvarchar(50),
             @ObjectID  INT
     
    SET @IndexSize = 0
    
    create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50))
    
    DECLARE curIndex CURSOR  FOR 
    SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc 
    FROM sys.indexes sysind 
    join sys.sysobjects sysobj
    on sysind.object_id = sysobj.id
    where sysind.type>0 and sysobj.type not in ('S','IT')
    order by sysobj.id asc
   
    OPEN curIndex 
     
    FETCH NEXT FROM curIndex 
    INTO @ObjectID, @IndexID, @IndexName, @IndexType
     
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) 
        FROM   sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') 
         
        insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType)
        SELECT TableName = OBJECT_NAME(@ObjectID), 
               IndexID = @IndexID, 
               IndexName = @IndexName,
               [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType
         
        FETCH NEXT FROM curIndex 
        INTO @ObjectID, @IndexID, @IndexName, @IndexType
      END 
    CLOSE curIndex 
    DEALLOCATE curIndex 
    
    select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)]  from #tmpresults order by [IndexSize(MB)] desc
  END 
GO

Posted in DB Maintenance, T-SQL | Tagged: , , , , , , | 2 Comments »

Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session

Posted by Sudarshan Narasimhan on December 14, 2012


I often use a multitude of T-SQL queries and scripts when I am troubleshooting memory issues and even for monitoring SQL Server Memory usage. I am posting these scripts here in the spirit of sharing, as I’m sure there are other variations of these same scripts out there already. For this purpose of this blog post, these scripts are only useful in troubleshooting out-of-memory (OOM) and other issues in the Buffer Pool. This blog post does not cover Virtual Address/Virtual Memory or memory tracking outside of the Buffer Pool. That will be covered in a later post.

In case you have other scripts for memory monitoring, please leave behind a comment

– Query to find the Buffer Pool usage per each Database
– Each of these pages are present in the Buffer Cache, meaning they are IN_RAM pages.

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE‘%Buffer Manager’
   AND counter_name = ‘Total Pages’;

;WITH BufCount AS
(
  SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [Database_Name] = CASE [database_id] WHEN 32767
       THEN‘MSSQL System Resource DB’
       ELSE DB_NAME([database_id]) END,
   [Database_ID],
   db_buffer_pages as [Buffer Count (8KB Pages)],
   [Buffer Size (MB)] = db_buffer_pages / 128,
   [Buffer Size (%)] = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC;

Output

image

-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25 
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 count(*)AS Buffered_Page_Count ,
 count(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC


Output

image

-- Query to show current memory requests, grants and execution plan for each active session
-- This shows memory granted & requested for currently active sessions on the instance level
-- This can be used in a script to capture information over a period of time.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Output

image

Note: When you click on the query_plan, it opens up the XML Showplan in SSMS.

-- Query to search plan cache for queries with memory grants completed

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Output

image

-- Queries that have requested memory or waiting for memory to be granted
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

Note: This query can be used when there are active sessions waiting on memory to be granted and they are waiting. These sessions will have a wait_type of RESOURCE_SEMAPHORE. You can calculate the wait time for memory grant, by subtracting request_time and grant_time

-- SQL Server 2005/2008/R2 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;
-- SQL Server 2012 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Output

image


That’s all for now folks!

- TheSQLDude (Sudarshan)

Posted in Memory, Performance, T-SQL | Tagged: , , , , , , , , , | 4 Comments »

Identifying Documents & Document Types that are NOT present in the full-text index

Posted by Sudarshan Narasimhan on August 29, 2012


I recently worked on this scenario with a customer of mine. They had a DOCS table which contained resume’s and CV’s of their candidates. This DOCS table was full-text indexed. They noticed that their search results in the application was not showing all the documents when they performed keyword based search. On looking into the full-text index and following my previous post on this topic @ http://thesqldude.com/2012/07/06/how-to-identify-the-row-or-document-that-failed-to-index-during-a-fulltext-index-population/ , we found out that many .DOC and .HTML documents have failed, hence these don’t show up in the search results.

Now, dbo.DOCS is a huge table and it contained more than a million rows. 1190688 to be exact. So to find out that documents that need to be replaced, I did the following steps.

GOAL

1. Identify all the document types and the count of documents of that type, which are not a part of the full-text index

2. Identify each DocumentID that is not a part of full-text index, so that this can be corrected from the application and thus re-index via fulltext population.

Luckily for me this was SQL Server 2008 R2. Starting with SQL Server 2008 there are 2 useful DMV’s that contain information about the full-text index.

a) sys.dm_fts_index_keywords_by_document

b) sys.dm_fts_index_keywords

These views were not present in SQL 2005. Using the DMV sys.dm_fts_index_keywords_by_document we can get to find the Document that is present in the FTIndex. If a document is present, it means it has been indexed successfully.

Table Structure

[dbo].[Docs]
    [DocId] [int](50)
    [DocName] [varchar](400)
    [DocType] [varchar](200) 
    [DocCategory] [varchar](50)
    [Document] [varbinary]

sys.dm_fts_index_keywords_by_document
    [document_id]  [int]
    [occurrence_count]   [int]
    [display_term]   [nvarchar (8000)]

The Document_id column in the DMV corresponds to the full-text key value of the table. In this case the table dbo.Docs has the full-text key column as [DocId]. So, basically the Docs.dbo (DocId) is the same as sys.dm_fts_index_keywords_by_document (document_id). If you want to find the full-text key column of a table, you can use a query like this within the context of the user database.

SELECT COL_NAME(object_Id('dbo.Docs'),OBJECTPROPERTY(OBJECT_ID('dbo.Docs'), 'TableFulltextKeyColumn'))

 

Identifying Indexed Documents, Failed Documents and Document Types

1. Find the count of documents that are successfully indexed

drop table #fts_index_document_id
CREATE table #fts_index_document_id (docid int)
insert into #fts_index_document_id
SELECT distinct document_id FROM sys.dm_fts_index_keywords_by_document(db_id('ContentDB'), object_id('dbo.Docs'))
go
select count(*) as [Successful Document Count] 
from dbo.docs_tbl_data t1
join dbo.fts_index_document_id t2
on t1.DocID = t2.DocID

I found that 979415 rows were returned from the above query.

Successfully Indexed Documents = 979415

2. Find the count of documents that are NOT successfully indexed, i.e. Failed Documents

select count(*) as [Failed Document Count]
from dbo.Docs t1
where t1.docid not in ( select t2.docid from #fts_index_document_id t2)

I found that 211273 rows were returned from the above query, which is basically a NOT IN filter where each document_id present in the base table (dbo.Docs) is checked in the FTindex (#fts_index_document_id) and if not present then it is a failed document.

Failed to Index Documents = 211273

Okay, so I did a count of the total rows present in the table dbo.Docs, and that came up to 1190688

Total Documents 1190688
Successfully Indexed Docs 979415
Failed to Index Docs 211273

So, the count is tallying up fine.

3. Find the count of each document type and the number of documents of that type that has failed to be indexed.

The idea here is basically to see which document types/formats are the ones that have failed to get indexed. You might notice patterns when finding this out, like all PDF docs have failed or something similar. To find out, I used this query

select DocType, COUNT(1) as [Count] from dbo.Docs
where DOCID NOT IN (select docid from #fts_index_document_id) 
group by DocType
order by COUNT(1) desc

image

As we can see from the above output, most of the failed documents are of type HTML and DOC.

4. Find each individual document, document name of a specific document type has failed to be indexed

Now that we have the breakdown of document types that have failed, we need to identify each unique document in the table of a specific type (say .DOC) that isn’t indexed, so that it can be corrected from the application side by re-inserting the document.

I am using the same # temp table which contains my indexed documents in the FTIndex. The reason I am using a #tmp table is because the DMV is slow as it scans through the full-text index and we don’t have to read the index each time. So I’m storing the output in a #tmp table and re-using that for all my queries. I would recommend you follow this because if the FTIndex is large, then querying the DMV each time isn’t going to be fast nor good for performance.

select t3.DocId, t3.Doctype, t3.DocName, t3.DocCategory
from dbo.Docs t3
where t3.DocID not in (
select t1.DocID from dbo.Docs t1
join #fts_index_document_id t2
on t1.DocID = t2.DocId)
and t3.DocType = 'HTML'
order by t3.DocId asc

The output of the above query will return each document that has failed to be indexed (not a part of the full-text index) and of type HTML. You can change the filter clause highlighted to suite your needs. See sample output below for a list of failed documents of type ‘DOC’.

image

Since you now the Document names, Document ID, Document type, you can get in touch with your application team to have a look at these documents and verify these are valid documents. If possible they can be reinserted back from the application into the table dbo.Docs.

NOTE

The thing with document indexing is that, all of these documents are first stored on the file-system and usually there is an application involved (any document management application) that takes care of inserting these documents into the database as BLOB data (varbinary, text, image). Usually its not inserted directly, but via some application. The application has to programmatically read the document as BYTE or Stream and then pass this to the database insert statement. There are chances that the application code has issues for certain documents, like maybe it missed a BYTE value or header. You will notice this scenario, when a particular document opens up fine using Office Client but the same document does not get indexed with this error

Error ’0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population

When you see this error, keep in mind the application that is actually taking the document and inserting it into the database. Whichever application is being used to insert the documents is not doing it correctly and the document is actually not valid for indexing. The IFilter for Office Documents is very strict on the file format. It will not even process other file formats that Word is able to open such as RTF. Opening the file with Office Client is very different to opening the stream for the IFilter. You will see this behaviour yourself when you open/save the same file without changes, Office Client adds other changes and filesize increases. It does make changes to the file header and this is a key point!

 
You may want to take a closer look to see how these DOC files are getting inserted into SQL Server. I’ve seen that even a small byte missed here or there can render the file invalid (in the eyes of the IFilter). I’ve seen this happen for XLS/PPT/DOC file formats. If you face this scenario, try and find answers to the following questions.

Question to Think about:-

1. What is the application used to insert these documents into the database?

Find the application vendor or support team and ask them if they have any application updates or any issues reported of specific documents types failing to get indexed

2. When were these failed documents inserted. Try and find the source date/year when these were added to the table (maybe a datetime column exists in the table?)

It is possible that all of the failed documents originated from a specific date and time. Usually this is the case for older file formats like DOC/PPT/XLS compared to the new Metro Office Formats like DOCX etc.

It is also possible that at the time these were inserted, the application performing the insert had some issues which were later fixed

3. Are there different versions of this application available?

Same as #2. If the application has later fixes, it doesn’t change the fact that the table contains BLOB value inserted using an older version of the application. Re-inserting the same documents using the newer version of the application is a step to take.

4. Are they facing failure with new documents being inserted today?

This will tell you if the issue is with older documents already present in the table or even with newly added documents. Again, we are just narrowing down the source of the problem.

 

Posted in FTS (Full-Text Search), T-SQL | Tagged: , , , , , , , , , , , | Leave a Comment »

Auditing T-SQL Script to find out who made changes to data in a table

Posted by Sudarshan Narasimhan on June 1, 2012


Having worked with multiple customers and different application where at times the data in the table has gotten changed and we need to find out when/where/who made the changes, I used the below T-SQL code to setup a trigger on the table and track the changes into an auditing table. There are obviously other ways to do this like SQL Profiler, XEvents etc., but I find using T-SQL more simpler and lesser overhead. So, sharing the script for others to use. 

-- Note: This trigger tracks Updates and Deletes happening on a table. 
-- Please delete this trigger once the source has been identified and corrective actions have been taken.

-- 1. Creating the audit table to store information on Update/Delete
CREATE TABLE AuditTable
(
AuditID [int] IDENTITY(1,1) NOT NULL,
Timestamp datetime not null CONSTRAINT AuditTable_Timestamp DEFAULT (getdate()),
OperationType char(1),
OperationDate datetime DEFAULT (GetDate()), 
PrimaryKeyValue varchar(1000), 
OldColValue varchar(200), 
NewColValue varchar(200), 
UserName varchar(128),
AppName varchar(128),
ClientName varchar(128)
)
go

--2. Creating  the audit trigger
-- Replace PrimaryKeyValue with the PK Column Name
-- Replace NewColValue with the column name in the IF BLOCK
-- Replace OldColValue with the column name in the final SELECT statement
-- Replace TBLNAME with the name of your table which you want to track the changes for.

Create trigger TBLNAME_Audit on TBLNAME for update, delete
AS
declare @OperationType char(1),
@OperationDate datetime,
@NewColValue varchar(200),
@OldColValue varchar(200),
@UserName varchar(128),
@AppName varchar(128),
@ClientName varchar(128)

select @UserName = system_user
select @OperationDate = CURRENT_TIMESTAMP
select @ClientName = HOST_NAME()
select @AppName = APP_NAME()

if exists (select * from deleted)
      if exists (select * from inserted)
      begin
            select @OperationType = 'U'
            select @NewColValue = select NewColValue from inserted
      end
      else
      begin
            select @OperationType = 'D'
            select @NewColValue = null
      end
      
Insert AuditTable (OperationType, OperationDate, PrimaryKeyValue, OldColValue, NewColValue, UserName, AppName, ClientName)
select @OperationType, @OperationDate, PrimaryKeyValue, OldColValue, @NewColValue, @UserName, @AppName, @ClientName
from deleted
go

--3. Query the audit table once the values in the base table has changed
select * from AuditTable

Posted in Auditing, Security, T-SQL | Tagged: , , , , , , | 2 Comments »

Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space

Posted by Sudarshan Narasimhan on May 15, 2012


Many times during the life of a DBA, you might notice the tempdb database growing excessively, though no changes have recently been done. It’s often the case that due to data increase, the application T-SQL queries are not written to scale up, hence end up doing excessive sorting/hashing operations which consume space from your tempdb database. Here are some T-SQL scripts that you can use to monitor who/what is consuming space from tempdb and plan accordingly.

Before we get into identifying queries that use tempdb, it is very important to understand what all activities in SQL Server (both internal & user activities), which use the tempdb database. Broadly you can classify these into 3 categories:-

  1. Internal Objects
  2. Version Stores
  3. User Objects

From a feature perspective, here are the features in SQL Server that use space from tempdb.

  1. Query
  2. Triggers
  3. Snapshot isolation and read committed snapshot (RCSI)
  4. MARS
  5. Online index creation
  6. Temporary tables, table variables, and table-valued functions
  7. DBCC CHECK
  8. LOB parameters
  9. Cursors
  10. Service Broker and event notification
  11. XML and LOB variables
  12. Query notifications
  13. Database mail
  14. Index creation
  15. User-defined functions

From a query performance standpoint, here are some operators that use tempdb space.

1. Sort Operator : The sort operator needs tempdb space to sort the full rowset of incoming rows. This is usually send when user ODER BY and also for DISTINCT ORDER BY

        image

2. Hash Match Operator: Depending on the size of row, a hash table could use tempdb

      image

3. Spool Operator: This operator is used to save the intermediate set of rows for re-use and uses the tempdb database to save the query result set.

         image

Tempdb out of space error

Error: 1105, Severity 17, State 2
Could not allocate space for object dbo.TBL1  in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full.

 

Identify which type of tempdb objects are consuming  space

The following query helps you understand if user objects or version store or internal objects are the ones using the space in tempdb. According to this output, you can focus on the below sections.

SELECT
SUM (user_object_reserved_page_count)*8 as user_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
If user_obj_kb is the highest consumer, then you that objects are being created by user queries like local or global temp tables or table variables. Also don’t forget to check if there are any permanent 
tables created in TempDB. Very rare, but I’ve seen this happening.
If version_store_kb is the highest consumer, then it means that the version store is growing faster than the clean up. Most likely there are long running transactions or open transaction (Sleeping state), 
which are preventing the cleanup and hence not release tempdb space back.



Query that identifies the currently active T-SQL query, it’s text and the Application that is consuming a lot of tempdb space

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id, 
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, 
er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 
 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) 
DESC



Tempdb and the Version Store

The version stored (SQL 2005 onwards) is a collection of objects that are used when Snapshot Isolation or Read-Committed Snapshot Isolation (RCSI) or online index rebuild etc. are used in a database.

Version store contains the committed rows which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row, because the SELECT reads the row from the version store, instead of the actual base table. When you enable this, the row has to be stored somewhere and tempdb happens to be the place. A row is maintained in the version store when there are transactions operating on that row in questions. When the transaction is committed, the row is cleaned up from the version store tables.

You can check the version store using the DMV sys.dm_tran_version_store

At times, when there are long running transactions or orphaned transactions, you might notice tempdb growth due to the version store.

You can use the following query to find the oldest transactions that are active and using row versioning.

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds,
b.program_name, b.open_tran, b.status
FROM sys.dm_tran_active_snapshot_database_transactions a
join sys.sysprocesses b
on a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

Trace Flag 1118

This trace flag is available starting with SQL 2000 SP3 to reduce tempdb contention by forcing uniform extent allocations as opposed to mixed extent allocations. This trace flag is only to be used if you seeing contention (wait_Stats) on the PFS/GAM pages like 2:1:1 etc.. More internal details on this trace flag is available in Paul Randal’s blog post here.

Not only does enabling the trace flag help but you need to create multiple tempdb files equal to the number of logical processors. So if you have 4 CPU’s you will create 4 tempdb data files. Now, what if you have 16 or 32 processors, do you still need to create that many tempdb files?

The answer is NO, you don’t have to. The above recommendation has been stated in many KB articles like http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551

If the number of logical processors on your server is greater than or equal to 8, then use 8 data files for tempdb. If the number of logical processors is less than 8, then use as many data files as your processor count.

You can use the following against any of the SQL Servers you manage to find out if any change is required in the tempdb data files to reduce contention and improve general performance.

Declare @tempdbfilecount as int;
select @tempdbfilecount = (select count(*) from sys.master_files where database_id=2 and type=0);
WITH Processor_CTE ([cpu_count], [hyperthread_ratio])
AS
(
      SELECT  cpu_count, hyperthread_ratio
      FROM sys.dm_os_sys_info sysinfo
)
select Processor_CTE.cpu_count as [# of Logical Processors], @tempdbfilecount as [Current_Tempdb_DataFileCount], 
(case 
      when (cpu_count<8 and @tempdbfilecount=cpu_count)  then 'No' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No'
      when (cpu_count>=8 and @tempdbfilecount=cpu_count)  then 'No (Depends on continued Contention)' 
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes'
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No (Depends on continued Contention)'
end) AS [TempDB_DataFileCount_ChangeRequired]
from Processor_CTE;

 

Here is a sample output. As you can see I have 8 processors and only 1 tempdb file. So I need to add 7 more files.

clip_image002


Last point before I wrap up this post. Once your tempdb database or log file is full, you have these options:-

1. Either you have to rollback any transactions consuming tempdb space or kill the transactions (not a good idea).

2. Create additional tempdb files in other drives which have free space, while you dig around to find the culprit who is growing tempdb.

3. Restart your SQL Server service.

Have fun working with tempdb. Here are some good references

Working with tempdb – http://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx

Storage Engine Blog – http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

Posted in Performance, T-SQL | Tagged: , , , , , , , , , | 6 Comments »

TSQL script to find Foreign Key Dependencies on entire database

Posted by Sudarshan Narasimhan on February 14, 2012


Often in the life of a DBA/Developer when we have to test something on a database, we are not always aware of the schema/design since it was created by someone else or you inherited the project. Lets says you want to test a particular SP execution time, but the data sample is too big for you to wait for the restore, so you want to insert some sample data. More often that not, the constraints defined do not let you to modify/add/remove data from the base tables. Well, you can’t pull up the entire database diagram and start studying the design.

It is not practical to do this on the entire database with Management Studio even though it offers a nice View dependencies option, but this is on a table by table basis. You would need to use T-SQL for this.

Here is a script which you can run on the entire database to understand which tables are dependent on your Table X,Y,Z and what are the columns being referenced (foreign keys) and what is the action on update/delete. You can uncomment the filter in the WHERE clause if you want to specify only some table names.

Script Modified: April 18, 2012

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',
       fk.referenced_object_id as ReferencedObjID,
       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS bit)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS bit)) as [ForeignSchema],
       [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END), 
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END)
FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(fk.referenced_object_id) = 'TBL_TEST'  --- table name which is being referenced by other tables via Foreign Keys
 ORDER BY OBJECT_NAME(fk.referenced_object_id)

 

Here is the how the output looks like on the AdventureWorks database

image

Posted in T-SQL | Tagged: , , , , , , | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 92 other followers