Feeds:
Posts
Comments

Archive for the ‘SQL System Table’ Category

SQL SERVER – Recently Executed T-SQL Query

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology, tagged on October 16, 2009 | Leave a Comment »

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 refer to following update query to [...]

Read Full Post »

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology on August 21, 2009 | 5 Comments »

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. [...]

Read Full Post »

SQL SERVER – Measure CPU Pressure – CPU Business

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on August 17, 2009 | 2 Comments »

Many a time, DBAs face the following question: can the CPU handle the current transaction? I have seen many DBAs getting confused on whether they should get a CPU with higher clock speed or more number of CPUs when it is time to upgrade system. As we all know, the CPU (or the Central Processing [...]

Read Full Post »

SQL SERVER – Get Last Running Query Based on SPID

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology on July 19, 2009 | 7 Comments »

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.
To know which sessions are running currently, run the following command:
SELECT @@SPID
GO

In [...]

Read Full Post »

SQL SERVER – Performance Counters from System Views – By Kevin Mckenna

Posted in Pinal Dave, Readers Contribution, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on June 10, 2009 | 1 Comment »

I just love social media and all the new concepts of Web 2.0. There are bloggers who are overwhelmed by the new concepts of technology and are not able to keep pace with it. But I like taking such challenges. Twitter has acquired tremendous popularity nowadays and just like everybody else I am also fond [...]

Read Full Post »

SQL SERVER – List All Objects Created on All Filegroups in Database

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on June 1, 2009 | 4 Comments »

When I pen down any article I always keep my readers in my mind. With every topic of SQL server I cover, I try to bring readers closer to this technology. So, whenever I receive follow up questions from my readers I am exhilarated! Sometime back I had covered a topic – SQL SERVER – [...]

Read Full Post »

SQL SERVER – List All the Tables for All Databases Using System Tables

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on April 26, 2009 | 7 Comments »

Today we will go over very simple script which will list all the tables for all the database.
sp_msforeachdb ’select ”?” AS db, * from [?].sys.tables’
Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)

Read Full Post »

SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged , on April 11, 2009 | 2 Comments »

This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs  that are associated with Transactions.
1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.
2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the [...]

Read Full Post »

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on April 1, 2009 | 16 Comments »

Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the [...]

Read Full Post »

SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, T SQL, Technology on March 21, 2009 | 9 Comments »

Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
SQL [...]

Read Full Post »

Older Posts »