SQL System Table Archives - Page 5 of 13 - SQL Authority with Pinal Dave

SQL SERVER – Resource Database ID – 32767

May 10, 2011

Earlier I blogged about SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object?. After reading the post, I got question by one of the blog reader. “Hi Pinal, I see in your blog post you have Database ID which is 32767. Everytime I want to get the…

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

March 23, 2011

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table

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…

SQL SERVER – System Stored Procedure sys.sp_tables

October 17, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL System Table

I have seen people running the following script quite often, to know the list of the tables from the database: SELECT * FROM sys.tables GO 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…

SQL SERVER – Find Total Number of Transactions on Interval

October 4, 2010

Pinal Dave

SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL Server, SQL System Table

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.

SQL SERVER – Get Query Running in Session

October 1, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table, SQL Utility

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…

SQL SERVER – Check the Isolation Level with DBCC useroptions

May 24, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table, Transaction Isolation

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…

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

April 20, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL System Table

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…