SQL SERVER – Denali – ObjectID in Negative – Local TempTable has Negative ObjectID

I used to run the following script to generate random large results. However, when I ran this on Denali I noticed a very interesting behavior:

SELECT o1.OBJECT_ID,o1.name, o2.OBJECT_ID, o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2

I noticed lots of negative object_ID’s on Denali, whereas my experience on SQL Server 2008 R2 as well as the earlier versions was it was always giving me a positive number.

This whole thing interested me so I decided to find out objects which belonged to the negative object_ID. When I looked at the name of the object, it was very evident that it belonged to TempTable.

To verify my finding, I ran the following command for a couple of times:


Then, I realized that every time I ran this command, I found one more negative ID added in the objects table. To further clarify this, I ran the following command:

FROM tempdb.sys.tables

This command really validated the event that whenever LocalTempTable is created in the Denali, it creates object_ID with negative number. However, in SQL Server 2008 R2 and earlier versions, it is always a positive number.

I attempted to create Global TempTable using the following code. The result was all of the global TempTable created Object_ID’s which yield positive numbers. For the moment, it looks like all the LocalTempTable have negative ID’s.

CREATE TABLE ##GlobalTempTable (ID INT)

Let me ask you a few questions:

  • Are there any other objects in SQL Server ‘Denali’ that have negative object_ID?
  • What can be the reason behind  the negative object_ID?

On a separate note, take a look at the following code:

IF OBJECT_ID('tempdb.dbo.#LocalTempTable') > 0

If you updated to Denali you should change it to either
OBJECT_ID('tempdb.dbo.#LocalTempTable') <>0

IF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – System Stored Procedure sys.sp_tables

I have seen people running the following script quite often, to know the list of the tables from the database:

FROM sys.tables

The script above provides various information from create date to file stream, and many other important information. If you need all those information, that script is the one for you. However, if you do not need all those information, I suggest that you run the following script:

EXEC sys.sp_tables

The script above will give all the tables in the table with schema name and qualifiers. Additionally, this will return all the system catalog views together with other views. This Stored Procedure returns all the tables first in the result set, followed by views.

Even though Stored Procedure returns more numbers of rows, it still performs better than the sys.table query.

Let us verify it with two different methods for database AdventureWorks:


USE AdventureWorks
FROM sys.tables
EXEC sys.sp_tables

(81 row(s) affected) (This is for sys.tables)
Table ‘syspalvalues’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syssingleobjrefs’. Scan count 0, logical reads 324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysidxstats’. Scan count 81, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syspalnames’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(456 row(s) affected) (This is for sys.sp_tables)
Table ‘sysobjrdb’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

2) Execution Plan

You can see that the execution plan for sys.table has much higher cost of query batch.

Well, if you only need to know the name of the tables, I suggest that you start using SP_TABLES; at least it takes less typing to do.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Total Number of Transaction on Interval

In one of my recent Performance Tuning assignment I was asked how do someone know how many transactions are happening on server during certain interval. I had handy script for the same. Following script displays transactions happened on server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.

-- First PASS
@Second INT
@First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'

If you are using any other method to detect transactions per interval, I request you to post it over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Get Query Running in Session

I was recently looking for syntax where I needed a query running in any particular session. I always remembered the syntax and ha d actually written it down before, but somehow it was not coming to mind quickly this time. I searched online and I ended up on my own article written last year SQL SERVER – Get Last Running Query Based on SPID. I felt that I am getting old because I forgot this really simple syntax.

This post is a refresher to me. I knew it was something so familiar since I have used this syntax so many times during my performance tuning project.

Run the following query to find out what the latest query that was executed in the session. There are various methods mentioned in my earlier post, so here I am picking only the one that I use most of the time.

Please use Shortcut – CTRL+T or enable “result to text” in the resultset to get formatted output.

SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)

You can find a range of active session IDs in your system by running system stored procedure sp_who2.

The following is the resultset where I have selected the session id that is the same as from where I am running above statement.

Additionally, you can use following T-SQL script as well.

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE session_id = (yoursessionID)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Check the Isolation Level with DBCC useroptions

In recent consultancy project coordinator asked me – “can you tell me what is the isolation level for this database?” I have worked with different isolation levels but have not ever queried database for the same. I quickly looked up bookonline and found out the DBCC command which can give me the same details.

You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.

DBCC useroptions

Set Option                  Value
--------------------------- --------------
textsize                    2147483647
language                    us_english
dateformat                  mdy
datefirst                   7
lock_timeout                -1
quoted_identifier           SET
arithabort                  SET
ansi_null_dflt_on           SET
ansi_warnings               SET
ansi_padding                SET
ansi_nulls                  SET
concat_null_yields_null     SET
isolation level             read committed

I thought this was very handy script, which I have not used earlier. Thanks Gary for asking right question.

Note: This optional will only give you isolation for current connection where the command has ran and not at server level.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit

During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double the CPU (add the same number of CPU that we have on current system). The same discussion has triggered this quick article.

Here is the DMV which can be used to find out Max Worker Count

SELECT max_workers_count
FROM sys.dm_os_sys_info

Let us run the above query on my system and find the results.

As my system is 32 bit and I have two CPU, the Max Worker Count is displayed as 512.

To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:

For x86 (32-bit) upto 4 logical processors  max worker threads = 256
For x86 (32-bit) more than 4 logical processors  max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors  max worker threads = 512
For x64 (64-bit) more than 4 logical processors  max worker threads = 512+ ((# Procs – 4) * 16)

In addition to this, you can configure the Max Worker Thread by using SSMS.

Go to Server Node >> Right Click and Select Property >> Select Process and modify setting under Worker Threads.

According to Book On Line, the default Worker Thread settings are appropriate for most of the systems.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning

Recompilation process is same as compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure. Recompilation occurs because of following reason:

  • On schema change of objects.
    • Adding or dropping column to/from a table or view
    • Adding or dropping constraints, defaults, or rules to or from a table.
    • Adding or dropping an index to a table or indexed view if index is used by the plan.
    • Adding or dropping trigger from a table
    • Dropping statistics from a table that is used in plan.
  • On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it. If the same stored procedure is executed by another connection that has a different SET option, then the existing cached plan is not reused. To reduce recompilation caused by Environment change, we should not change the SET options in the connection setting and stored procedure.
  • On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables. It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized. For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created.

Detecting recompilations: The below query retrieves the top 10 statements for which the recompilation count is maximum. Here, plan_generation_num returns a number that indicates the recompilation count of a statement.

DB_NAME(st.dbid) AS DbName,
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC

Another tool is to get recompilation details is the SQL Profiler. Along with the statements text, it also tells the reason of recompilation in EventClass and EventSubClass columns.

Reducing recompilations: We have no option to avoid “schema change” and “SET options” based recompilation. But we have following query and stored procedure hints to avoid “statistics change” based recompilation:

  • KEEP PLAN hint: The modification counter threshold for a temporary table is 6. This implies that when a stored procedure that creates a temporary table inserts 6 or more rows into this table, Stored Procedures will be recompiled as soon as this table is accessed. For permanent tables, this threshold is at least 500. We can increase the first threshold for the temporary table (6) to same as that of the permanent table (500) by using the KEEP PLAN query hint in the statement where the temporary table is used. For example,

SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
ON PermTable.col1 = TT.col2

  • KEEPFIXED PLAN hint: This hint completely avoids “statistics change” based recompilation of a query.

SELECT col1, col2
FROM dbo.PermTable
WHERE col3 &lt; 100

  • Use Table variable instead of temporary tables: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table when faced with a problem of excessive recompilation. However, the use of table variables can lead to poorer query plans. Distribution statistics are not stored for table variables, and cardinality is only available during recompilation and not during initial compilation. One has to check whether this is the case and make an appropriate trade-off.

Reference: Pinal Dave (http://blog.SQLAuthority.com)