Feeds:
Posts
Comments

Archive for the ‘SQL System Table’ Category

SQL SERVER – Resource Database ID – 32767

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on May 10, 2011 | 10 Comments »

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 name of the database from database_ID I use following function but this time this function returned NULL.

SELECT DB_NAME(32767)

When I tried to list all the databases uses following script it did not have that database ID as well.

SELECT *
FROM sys.databases

I assume you have created this many database is that true?”

Very interesting question to me. I have never thought about it when I posted as I assumed few things. Here is the answer. Database_ID 32767 is reserved Resource Database.I have not created that many databases. This database is hidden from users in SSMS but you can see that if you go to file folder. You can read more about the same over here SQL SERVER – Location of Resource Database in SQL Server Editions.

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. (from BOL).

In SQL Server maximum databases per instances can be created are 32,767. This last number has been reserved by Resource Database itself.

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

About these ads

Read Full Post »

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

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on March 23, 2011 | 7 Comments »

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:

CREATE TABLE #LocalTempTable (ID INT)

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:

SELECT *
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
IF OBJECT_ID('tempdb.dbo.#LocalTempTable') <>0
or
IF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL

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

Read Full Post »

SQL SERVER – System Stored Procedure sys.sp_tables

Posted in Technology, SQL Server, SQL, SQL Query, SQL Stored Procedure, SQL Tips and Tricks, SQL Authority, Pinal Dave, T SQL, SQL Scripts, SQL System Table on October 17, 2010 | 6 Comments »

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 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
GO

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:

1) SET STATISTICS IO ON

USE AdventureWorks
GO
SET STATISTICS IO ON
SELECT *
FROM sys.tables
GO
EXEC sys.sp_tables
GO

(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)

Read Full Post »

SQL SERVER – Find Total Number of Transaction on Interval

Posted in SQL, SQL Authority, SQL DMV, SQL Performance, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on October 4, 2010 | 6 Comments »

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
DECLARE @First INT
DECLARE @Second INT
SELECT @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'
GO

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)

Read Full Post »

SQL SERVER – Get Query Running in Session

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQL Utility, SQLServer, T SQL, Technology on October 1, 2010 | 1 Comment »

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.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

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.

SELECT TEXT
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE session_id = (yoursessionID)
GO

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

Read Full Post »

« Newer Posts - Older Posts »