Identity Values–How close to the edge are you?

How close is your data to the edge! Integer identity columns that increment by 1, have been known to run out of numbers. I have seen it occur on a few occasions on highly transactional systems. However there is really no excuse for getting caught out.

The code below will show all your tables that have an identity seed on them and how full they are percentage wise. The trick is to catch them before they hit 100% and bring down the database as it can’t insert any more rows!.

There are several options to fix, change the data to bigint, reseed the values ( if you don’t keep all the data such as archiving and you don’t need a unique ID) to a lower value,  or even start on a negative identity number to double the capacity!, but somehow that seems wrong. If it had been planned correctly it would already be the right data type!. Hey these thing happen you inherit systems

Exact number data types that use integer data.

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 – 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

SQL 2000 code

SELECT
    QUOTENAME(USER_NAME(t.uid))+'.'+QUOTENAME(t.name)AS TableName,
    c.name AS ColumnName,
    CASE c.xtype
    WHEN 127 THEN 'bigint'
    WHEN 56 THEN 'int'
    WHEN 52 THEN 'smallint'
    WHEN 48 THEN 'tinyint'
    END AS 'DataType',
    IDENT_CURRENT(USER_NAME(t.uid)+'.'+ t.name) AS CurrentIdentityValue,
    CASE c.xtype
    WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid)+'.'+ t.name)* 100.)/ 9223372036854775807
    WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid)+'.'+ t.name)* 100.)/ 2147483647
    WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid)+'.'+ t.name)* 100.)/ 32767
    WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid)+'.'+ t.name)* 100.)/ 255
    END AS'PercentageUsed'
FROM
    syscolumns AS c
INNER JOIN
    sysobjects AS t ON t.id = c.id
WHERE
    COLUMNPROPERTY(t.id, c.name,'isIdentity')= 1
AND
    OBJECTPROPERTY(t.id,'isTable')= 1
ORDER BY
    PercentageUsed DESC

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

SQL 2005/2008

 

SELECT
    QUOTENAME(SCHEMA_NAME(t.schema_id))+'.'+ QUOTENAME(t.name)AS TableName,
    c.name AS ColumnName,
    CASE c.system_type_id
    WHEN 127 THEN 'bigint'
    WHEN 56 THEN 'int'
    WHEN 52 THEN 'smallint'
    WHEN 48 THEN 'tinyint'
    END AS'DataType',
    IDENT_CURRENT(SCHEMA_NAME(t.schema_id)+'.'+ t.name) AS CurrentIdentityValue,
    CASE c.system_type_id
    WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name)* 100.)/ 9223372036854775807
    WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
    WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name)* 100.)/ 32767
    WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name)* 100.) / 255
    END AS'PercentageUsed',
    CAST(CAST(GETDATE()as VARCHAR(12))as datetime) as ReportTime
FROM
    sys.columns AS c
INNER JOIN
    sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE
    c.is_identity = 1
ORDER BY
    PercentageUsed DESC

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Knowledge Week – Server values, Tables with NULL data type, IO Stats in ASPstate

Maximum Capacity details

Maximum capacity specifications for SQL Server 2005, useful document, showing sizes and maximums values of various elements. For example maximum DB size, number of columns in a select statement, number of nested queries, tables per select statement, which I did once work with someone who hit this limit and was proud of the fact, to say it didn’t stop him as he inserted into a temp table and carried on joining tables in!.

http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

Tables with No data types

Interesting issue occurred with a migration of an OLAP SQL server from SQL 2000 to SQL2008. The installation was a clean install of SQL 2008 Enterprise, with a variety of user scratch databases. These databases during the testing phase were backed up on SQL2000 and restored to the 2008 Instance and the following script ran for each DB in turn to bring up to SQL2008.

USE
[master]

GO

ALTER
DATABASE
[USERDB]
SET
COMPATIBILITY_LEVEL
= 100

GO

USE
USERDB

GO

sp_updatestats

GO

DBCC
UPDATEUSAGE
(USERDB)

GO

The sp_update stats showed errors with the following issue on on of the user tables names being changed to protect the innocent

Msg 6261, Level 16, State 1, Line 1

The CLR type referenced by column “Canceldate” of table variable “dbo.UserTable” has been dropped during the execution of the batch. Run the batch again.

Googling the issue and error number shows a link to Microsoft errors very usefully tells you exactly the error message shown above. Further investigation shows that attempting to script the table gives the same error. This obviously is a red-herring as the data is from SQL 2000 which doesn’t have CLR data types. Interesting is if you look at the table definition either through highlighting the table in Management studio and ALT+F1 or using the object browser it shows the data type as type NULL

Going back to the original database and looking at the source table, it also shows that the data type is NULL!

After investigation it was discovered the source of the table was another database which was restored to the server for reporting purposes, of which the data type was a user defined data type called udt_timestamp, of type DateTime. The user who created the table was running SELECT INTO statements, another reason to add to the list for not using these!!

The SQL2008 data was pretty much corrupted, a select against the table resulted in the 6261 error, couldn’t script the table, CHECK Table showed no issue, the best way was to fix the source data and re-backup and restore, what would occur on go live day anyway.

The options to fix was

1. to apply the correct data type in the source database

ALTER
TABLE UserTable

ALTER
COLUMN UserColumn DATETIME

1. Try and educate the user to create the tables before hand and not use UDF’s that don’t exist in the database they are populating
2. Create UDF’s all over the shop on the new server.

Option 1 was chosen, which resolved the issue.

I/O Stats

Couple of queries mentioned in the last blog posted her, from SQLServer magazine, this helped sort out a curious waste of I/O in the ASPstate database. It showed that 40% of the I/O being consumed was from the log drive and the ASPState database. ASPstate is a transient database in that its recreated each time SQL was started, but it was set as full recovery, even though it would never been restored under any circumstances.

–==========================================================

–Display IO Stats as a % across all databases

–Database , io in mb, % used

–==========================================================

WITH
Agg_IO_Stats

AS

(

SELECT

DB_NAME(database_id)
AS database_name,

CAST(SUM(num_of_bytes_read +
num_of_bytes_written)
/ 1048576.AS
DECIMAL(12, 2)) AS io_in_mb

FROM

sys.dm_io_virtual_file_stats(NULL,
NULL)
AS
DM_IO_Stats

GROUP
BY

database_id

)SELECT

ROW_NUMBER()
OVER(ORDER
BY
io_in_mb
DESC) AS
row_num,

database_name,

io_in_mb,

CAST(io_in_mb / SUM(io_in_mb)
OVER()
* 100 AS
DECIMAL(5, 2)) AS pct

FROM

Agg_IO_Stats

ORDER
BY

row_num;

–==========================================================

–I/O usage by drive letter

–==========================================================

WITH
g
As

(

SELECT
db_name(mf.database_id)
as database_name,

mf.physical_name,

left(mf.physical_name, 1)
AS drive_letter,

vfs.num_of_writes,

vfs.num_of_bytes_written
AS
BYTESWRITTEN,

vfs.io_stall_write_ms,

mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read,

vfs.io_stall_read_ms,

vfs.io_stall,

vfs.size_on_disk_bytes

FROM

sys.master_files
mf

JOIN

sys.dm_io_virtual_file_stats(NULL,
NULL)
vfs
ON
mf.database_id=vfs.database_id and mf.file_id=vfs.file_id

— order by vfs.num_of_bytes_written desc)

)SELECT

database_name,

drive_letter,

BYTESWRITTEN,

Percentage
=
RTRIM(CONVERT(DECIMAL(5,2),

BYTESWRITTEN*100.0/(SELECT
SUM(BYTESWRITTEN)
FROM g)))

–where drive_letter=’R’)))

+
‘%’

FROM

g
–where drive_letter=’

ORDER
BY
BYTESWRITTEN
DESC

Self Referencing Keys

Another interesting one was a financial table that had a self referencing key, so child rows referred to a parent ID row within the table, it was discovered that a delete on a single row on using the clustered key was taking up to 50 seconds against a table with 58 million rows, the select on the same clustered row, instantaneous. When setting statistic IO on it was shown the logical reads were ½ million when the FK was applied and 4 when removed

The fix was to add a none clustered to the column on the FK. A best practice which hadn’t been adhered to, causing the issue.

Knowledge Week – TempDB, I/O Stats, statistics only database

In an attempt to blog a bit more and use this resource as source in the future of things I located and didn’t bother bookmarking. Also through a recent stint of interviewing and asking potential candidates for DBA roles ” What do you do to keep yourself up to date in what’s happening in the world of SQL “, ” what articles have you read recently that interested you”, ” what SQL resources / websites do you regularly visit?” , I thought what if I was asked that question. Well the answer is not as much as I like, or have the time to do, and I should do more. I have lots of resources that I can utilise, but don’t well that’s changing.

So each week I’ll put together all the stuff I have had time to browse, either as part of my job as a day to day DBA, or out of hours things I have been looking into

TempDB

Building a new 64bit SQL 2008 server and looking into potential benefits of losing redundancy over performance on an OLAP server by breaking the mirrored disk and splitting out the data files, came across good Tempdb information at http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx

Database Cloning

Also there was an article in SQL Server magazine that could be useful one for the future on creating a clone database, basically a cut down version of the database , consisting of the scheme and all the statistics, so you can run execution plans against a small database as if it was the live database. SQL 2005/2008 (March SQL server Magazine). Link http://support.microsoft.com/default.aspx?scid=kb;EN-US;914288

Database uptime ( 4 different ways, ok 3)

I have always used the sysprocesses table for the last time SQL was started, and selecting SPID 1, I saw a variation on this theme using the Lazy writer process, I don’t if these processes will ever get out of sync, in that the first spid is always the Lazy Writer but I suspect that could be the case

–Get the start process time Method 1

SELECT

login_time

FROM

master..sysprocesses

WHERE

Spid =1

–Get the start process time Method 2

SELECT

login_time

FROM

master..sysprocesses

WHERE

cmd=‘LAZY WRITER’

Another interesting way is to use the following, by getting the timestamp from the built in function fn_virtualFileStats that’s entered when SQL starts, each file gets the same timestamp, so just needs to use minimum and then convert to seconds and subtract from the current time to workout the start time. There will probably be a difference between method 3 and the previous two.

–Get the start process time Method 3

SELECT

DATEADD(ss,
–1 * min(Timestamp)/1000 ,
getdate())
AS [Start per fn_virtualfilestats]

FROM

::fn_virtualfilestats(-1,
–1)

Another option could be to look at the start of the SQL Error log , however it must be noted that you can manually recycle the error log using sp_cycle_errorlog which would mean the first entry in the log is not necessarily when the server was stopped and started. I personally have setup production servers to recycle the log daily and changed the default 6 logs to a more appropriate value. This is useful then, as the running the command below gives you the relevant log and you can go back through previous logs by suffixing 1,2 etc. Also when a log gets huge its quicker through T-sql that management studio to access.

–Get the start process time Method 4

EXEC
xp_readerrorlog

How Close to the edge are you Identity columns

How close is your data to the edge! Integer identity columns that increment by 1, have been known to run out of numbers. I have seen it occur on a few occasions on highly transactional systems. However there is really no excuse for getting caught out.

The code below will show all your tables that have an identity seed on them and how full they are percentage wise. The trick is to catch them before they hit 100% and bring down the database as it can’t insert any more rows!.

I’ll go through the options how to fix and how you can double the capacity in a future post

Exact number data types that use integer data.

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 – 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

SQL 2000 code

SELECT

QUOTENAME(USER_NAME(t.uid))+‘.’+QUOTENAME(t.name)AS TableName

c.name AS ColumnName,

CASE c.xtype

WHEN 127 THEN ‘bigint’

WHEN 56 THEN ‘int’

WHEN 52 THEN ‘smallint’

WHEN 48 THEN ‘tinyint’

END AS ‘DataType’,

IDENT_CURRENT(USER_NAME(t.uid)+‘.’+ t.name) AS CurrentIdentityValue,

CASE c.xtype

WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid)+‘.’+ t.name)* 100.)/ 9223372036854775807

WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid)+‘.’+ t.name)* 100.)/ 2147483647

WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid)+‘.’+ t.name)* 100.)/ 32767

WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid)+‘.’+ t.name)* 100.)/ 255

END AS‘PercentageUsed’

FROM

syscolumns AS c

INNER JOIN

sysobjects AS t ON t.id = c.id

WHERE

COLUMNPROPERTY(t.id, c.name,‘isIdentity’)= 1

AND

OBJECTPROPERTY(t.id,‘isTable’)= 1

ORDER BY

PercentageUsed DESC

SQL 2005/2008

SET
NOCOUNT
ON

SELECT

QUOTENAME(SCHEMA_NAME(t.schema_id))+‘.’+ QUOTENAME(t.name)AS TableName,

c.name AS ColumnName,

CASE c.system_type_id

WHEN 127 THEN ‘bigint’

WHEN 56 THEN ‘int’

WHEN 52 THEN ‘smallint’

WHEN 48 THEN ‘tinyint’

END AS‘DataType’,

IDENT_CURRENT(SCHEMA_NAME(t.schema_id)+‘.’+ t.name) AS CurrentIdentityValue,

CASE c.system_type_id

WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name)* 100.)/ 9223372036854775807

WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name) * 100.) / 2147483647

WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name)* 100.)/ 32767

WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name)* 100.) / 255

END AS‘PercentageUsed’

,CAST(CAST(GETDATE()as VARCHAR(12))as datetime) as ReportTime

FROM

sys.columns AS c

INNER JOIN

sys.tables AS t ON t.[object_id] = c.[object_id]

WHERE

c.is_identity = 1

ORDER BY

PercentageUsed DESC

Free Training – Well Almost and Plan Cache Function

I attended Dev week in February at the Barbican in London, and as part of some free draw won a subscription to training site for a year. It’s September and I have not really utilised this free resource. It’s mainly around programming but now I am actually looking at the SQL content I will post any useful stuff I come across. The Site is http://www.PluralSight.com

CREATE FUNCTION SQLAndPlan
(@handle varbinary(max))

RETURNS TABLE

AS

RETURN

SELECT

sql.text,

cp.usecounts,

cp.cacheobjtype,

cp.objtype,

cp.size_in_bytes,

qp.query_plan

FROM

sys.dm_exec_sql_text(@handle)as
SQL

CROSS
JOIN

sys.dm_exec_query_plan(@handle)
as qp

JOIN

sys.dm_exec_cached_plans
as cp ON cp.plan_handle =@handle;

and the view

CREATE
VIEW PlanCache

AS

SELECT

sp.*

FROM

sys.dm_exec_cached_plans
as cp

CROSS
APPLY

sqlandPlan(cp.plan_handle)
as sp

Query the view for the SQL statement in the cache the number of times the plan has been used while in the cache, type of plan, size of plan and the xml of the plan

SELECT  * FROM planCache

I have a query I use for the same reason, which I will post at some stage.

Blog posted by Martin Croft ( code from PluralSight.com Author Dan Sullivan)

Filegroups – What filegroups my tables reside in

I have in the past started but never completed an easy way of finding out tables in filegroups. The usual case was I need to know quickly what table was in a filegroup and not having time I resorted in just scripting of the objects to management studio and looking at he create script for the file group. I did actually get chance one time and the following was the result. No need to reinvent the wheel as there is a system stored procedure, always a good place to start! That does most of the work, this procedure sp_objectfilename found by tracing the create script routine in Management Studio takes and object ID. It was not difficult to pull out the bits needed and add in sysobjects and there you go a script that gives you the table and the filegroup it resides in.

SELECT

o.name AS ObjectName,

s.groupname AS Data_located_on_filegroup

FROM

sysfilegroups s

JOIN

sysindexes i ON i.groupid = s.groupid

JOIN

sysobjects o ON o.id=i.id

WHERE

i.indid < 2 AND o.type
in
(‘U’,‘V’)

ORDER
BY 1

Desc

Author Martin Croft

FILESTREAM & Sp_filestream_configure

This will the first of several post on Filestream as I look into implementing on a system used for documentation storage.

FILESTREAM & sp_filestream_configure

— Pre RTM release of SQL the stored procedure sp_filestream_configure existed

— for the configuration of the FILESTREAM settingsm there are a variety of example

— out on the web referencing this stored procedure

— this has been removed, even though it still turns red, on the RTM version

EXEC sp_filestream_configure @Enable_level=2, @Sharename =’SQL2008′

–This is now controlled through sp_configure

–You will need to enable advanced options if not already set

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE

–This functionaly is now in the filestream_access_level option

–Values for this option are set out below

0 Disables FILESTREAM support for this instance.

1 Enables FILESTREAM for Transact-SQL access.

2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.

–Example of enabling FILESTREAM for TSQL and WIN32 Access

EXEC sp_configure filestream_access_level, 2

GO

RECONFIGURE

Author Martin Croft

SQL Server Versions

Version	Release	Sqlservr.exe
SQL 2008
	SQL Server 2008 Service Pack 1	2007.100.2531.0
	SQL Server 2008 RTM	2007.100.1600.0
SQL 2005
	SQL Server 2005 Service Pack 3	2005.90.4035
	SQL Server 2005 Service Pack 2	2005.90.3042
	SQL Server 2005 Service Pack 1	2005.90.2047
	SQL 2005 RTM	2005.90.1399
SQL 2000
	SQL Server 2000 SP4	2000.8.00.2039
	SQL Server 2000 SP3a	2000.80.760.0
	SQL Server 2000 SP3	2000.80.760.0
	SQL Server 2000 SP2	2000.80.534.0
	SQL Server 2000 SP1	2000.80.384.0
	SQL 2000 RTM	2000.80.194.0

Author Martin Croft

SQL Server Top 10 Queries

This is a list of the top 10 queries I use on a frequent basis that are relatively simple, either one liners or next to one liners. These are in reverse order from 10 through my favourite 1.

10 Job Information

You want to quickly access job information, you don’t want to have to trawl through joining MSDB.dbo.sysjobs , MSDB.sysjobsteps, just want a quick overview of what jobs are enabled/disabled or what job was updated yesterday? Or when it that job last run or next run

–Name,Enabled, description, owner, modeified date,etc

EXEC MSDB.dbo.sp_help_job

–Or specify Job_id and get step details, schedule times

EXEC MSDB.dbo.sp_help_job ’34DD4F82-423C-46E9-9E9A-BF7786′

9 Quick Search

Trying to work out where a particular column or table is called in a procedure, you can check the dependency’s or another option is to just quicker check syscomments for the text you are after, there are several procedure out on the web that add a high degree of search ability but this is fast and easy

USE NorthWind;

GO

SELECT
      OBJECT_NAME(iD),text
FROM
      syscomments
WHERE
      Text LIKE ‘%sales%’

8 Traces running

I have seen it before your running a trace, the trace hangs is it still running, how can it be you have closed the crashed profiler, always safer to check. or is big brother watching! one way to check! See BOL for the output to this function

SELECT * FROM ::fn_trace_getinfo(default)

7 Am I sysadmin

Maybe not useful for everyone, but I have had uses for this multiple times, especially with a variety of access accounts and SQL2005 /2008 ability to switch environments quickly (right click and change connection). I find it useful to know if the account I am logged in has Sysadmin as a usual theme with SQL there are a various ways of finding this out, here is one I use.

–System Admin 1 yes god like powers 0 no I can’t drop that database

SELECT is_srvrolemember(‘sysadmin’) [Sysadmin]

6 System Uptime

Is the system running like a dog? People start asking when was SQL rebooted, I.T’s magic wand lets reboot the server, when was SQL last started. There are several ways of finding out this information ( can use Top tip 5 as well!) but this is an easy approach. Basically see when SPID 1 logged in, also if you use DATEDIFF you can get SQL to tell you how many days, how many DBA’s can count I even use SELECT 10+20 to work out calculations, far too slow opening up calculator.

–Logintime for SPID 1

SELECT DATEDIFF(dd,login_time,Getdate()) Uptime,Login_time

FROM master..sysprocesses

WHERE spid =1

5 Errorlogs

Sometimes useful when evaluating an issue, the SQL error logs can be access from the object explorer, but can be quicker and especially if it’s a long log it open far quicker this way.

–Read Error log takes Integer value for the error log number

exec master..xp_readerrorlog

 

Useful to find out a variety of information quickly, the log gets recycled when the server reboots, header shows were these logs are actually kept, version of SQL and a variety of message. If database are in recovery good place to look to get idea how long its going to take

4 Statistics

Lifted directly from the pages of the SQL bible, or BOL as it known. Things are running pants trying to work out what has changed, how up to date are the statistics? This will tell you.

–STATS_DATE code from BOL

SELECT

‘Index Name’= i.name,‘Statistics Date’=STATS_DATE(i.id, i.indid)

FROM

sysobjects o

JOIN

sysindexes i ON o.id = i.id

I tend to use order by 2 DESC added onto the end to order by the tables that were last updated.

3 Disk Space

Another one of those problem solving procedures. Used quite frequently on development system, as you really should have no excuses for production systems running out of space, unless it is the log drive and something untoward has occurred. One of the first procedures run when a developer saymy database restore won’t work, 90Gb doesn’t fit on 45Gb free space funnily enough!

–List disk information, useful for those users filling logs!

EXEC
Master.dbo.xp_fixeddrives

2 Short Cut Keys

Life savers when reviewing production incidents, how often you get “it’s not working” with little or no information, multiple systems that you’re unsure of the exact schema, so it is useful to know short cut keys. These are some of the ones I use daily. By assigning to short cuts you can specify, by highlighting SQL I can quickly pull up lots of useful info

–Get the stored procedure text of system proc sp_who , just highlight sp_who & press Ctrl-F1

USE
MASTER;

GO

sp_helptext
sp_who

— Get infomation on tables , Highlight Region & Press ALT +F1

USE NorthWind

GO

Region

1 Quick Blocking

The piece of code that I probably use more than most, so simple but so helpful in times of crisis, which was basically many years ago ripped off from the system stoted procedure EXEC sp_blockcnt, which basically just tells you the number of blocked processes on ther server. This querys can be written from memory with no need for fancy solutions on production boxes that your can’t role out code to.

–Blocking processes

SELECT
*
FROM
MASTER.dbo.sysprocesses
WHERE blocked <> 0

TOP 12 SQL Server Short Cut Keys

The top 12 Shortcut keys that I use( ok was 10 but found an additional 1 and could not leave it at 11 as it seemed wrong)

These can be life savers when reviewing production incidents, how often you get “its not working” with

little or no information, or when on multiple systems your unsure of the exact schema, so its useful

to know short cut keys. These are some of the ones I use daily. You can also assig short cuts to system procedures

In Reverse Order

11 Web Browser! default to MSDN blog site

CTRL & ALT + R

10 Execution Plans

Show execution plans either estimated or Actual through Query window

CTRL + L Estimated Execution plan

CTRL + M Actual Execution plan

9 Design Query In Editor

Feeling Lazy today?

CTRL + SHIFT +Q

8 Results Ouput

CTRL+T Results to text

CTRL+D Result to Grid

CTRL+SHIFT+S Result to file

8 Results Pane

Toggle the query results pane off and on

CTRL+R

7 Solution Explorer

Quickly bring up all your solutions SQL2005/2008

CTRL+ALT+L

6 UPPER CASE & lower case SHIFT+CTRL+U and shift+ctrl+l

Reading other peoples SQL can be a nightmare, formatting and reservered words in lower/ upper case etc

you can global search and replace, but how many times have you done that and its replaced other words.

dont be lazy do it as you go along and use SHIFT+CTRL+U and shift+ctrl+l if needed

Just highlight the text and use the SHIFT+CTRL+U and shift+ctrl+l combinations

5 Switching Windows ALT+F6

When you have mutiple query windows cycle through them using

ALT +F6

4 Wheres my Server ALT + F8

When connected to SQL Management Studio writing some SQL in SQL2005 /2008 and you swicth connection

to another server, it used to be a pain having to connect the object browser and connect to the same

server just so I can acces Enterprise Manager bit of management studio, no longer

ALT +F8

This brings up the server you are connected to in the object explorer

3 Get the stored procedure text of system proc sp_who , just highlight sp_who & press Ctrl-F1

This works just like running the following code or just highlight sp_who text while in master and hit CTRL+F1

USE MASTER;

GO

sp_helptext sp_who

2  Get infomation on tables , Highlight Region & Press ALT +F1

USE NorthWind

GO

Region

1 Trace Query in Profiler  (2008 only)

As I was putting this together I was looking through the tool bars and noticed this

how great is this feature and I have never used it before. Everyday is a School day. Straight

in at number 1, it opens up profiler and filters for your current SPID wow

CTRL+ALT +P

Author Martin Croft