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

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

March 21, 2009

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

Best Practices, SQL Scripts, SQL Stored Procedure, SQL System Table

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…

SQL SERVER – Find Current Location of Data and Log File of All the Database

February 17, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Scripts, SQL Server Security, SQL System Table

As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary…

SQL SERVER – List All Server Wide Configurations Values

February 16, 2009

Pinal Dave

SQL Tips and Tricks

SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table

Just a day ago, while working on one of the project, I needed to see what is the two digit year cutoff of my current SQL Server. I did not remember what was the exact syntax to search for the same so I ran following query to list all server wide configurations. While looking at quickly I found out value of two digit year cutoff on line 19th. A small but very important script to save for getting server information.

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

January 23, 2009

Pinal Dave

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

Best Practices, SQL Scripts, SQL Server DBCC, SQL System Table

Just another day I was playing with my query which I posted earlier SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL and I found that I got error devide by zero. I have fixed this error in following query as well I have updated query…

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

January 13, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL System Table, SQL Utility

I have written following script every time when I am asked by our team leaders or managers that how many rows are there in any particular table or sometime I am even asked which table has highest number of rows. Being Sr. Project Manager, sometime I just write down following…

SQL SERVER – Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures

September 28, 2008

Pinal Dave

SQL Tips and Tricks

SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table

I have written many articles about renaming a table, columns, and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. Let us learn about how renaming stored procedure does not update sys.procedures.

SQL SERVER – Find Primary Key Using SQL Server Management Studio

September 6, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Constraint and Keys, SQL Index, SQL Scripts, SQL System Table

Imran Mohammed is great SQL Expert and always eager to help community members. He enjoys answering question and solving problems of other community fellows. His answers are always detailed and trustworthy. Today we will see interesting question from Prasant and excellent answer from Imran Mohammed. Question from Prasant: Hi, I…