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

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

April 20, 2010

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…

SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning

February 18, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL Stored Procedure, SQL System Table

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…

SQL SERVER – Find the Size of Database File – Find the Size of Log File

February 8, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Log, SQL Scripts, SQL System Table

I encountered the situation recently where I needed to find the size of the log file. When I tried to find the script by using Search@SQLAuthority.com I was not able to find the script at all. Here is the script, if you remove the WHERE condition you will find the…

SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines

February 4, 2010

Pinal Dave

SQL Tips and Tricks

SQL Dependency, SQL Scripts, SQL Server, SQL Server Security, SQL System Table

Recently, I read a question on my friend‘s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema. routines do give proper answers.

SQL SERVER – Location of Resource Database in SQL Server Editions

January 31, 2010

Pinal Dave

SQL

SQL Scripts, SQL Server, SQL System Table

While working on a project of database backup and recovery, I found out that my client was not aware of the resource database at all. Location of Resource.

SQL SERVER – Recently Executed T-SQL Query

October 16, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Function, SQL Scripts, SQL System Table

About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER. The Short Answer in is both of them will be deprecated. Please…

SQL SERVER – Get Query Plan Along with Query Text and Execution Count

August 21, 2009

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server, SQL System Table

Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.