sp_MSforeachtable

Find out fragmentation levels for all tables in your database by running the undocumented stored procedure sp_MSforeachtable

-- Create temp table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

--Insert fragmentation level information
sp_MSforeachtable 'insert into #fraglist exec(''DBCC showcontig (''''?'''') with tableresults '') '

--Get all the info back
SELECT * FROM #fraglist

After you have run this you can determine which tables should be defragmented by looking at the AvgFreeBytes, ScanDensity, LogicalFrag and ExtenFrag columns

Find All Tables Without Triggers In SQL Server

Sometimes you want to change tables by adding columns, dropping or changing column types
You want to make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make these changes
I present two ways to accomplish that, the first way is by joining the INFORMATION_SCHEMA.TABLES view with the sysobjects system table.
The second way is to do a self join on the sysobjects system table. Both of these queries will return the same result


SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(SELECT OBJECT_NAME(o.parent_obj) AS TableName
FROM sysobjects o
WHERE OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
ORDER BY t.TABLE_NAME

SELECT s1.name FROM sysobjects s1 LEFT JOIN sysobjects s2 ON
s1.id =s2.parent_obj
AND s2.xtype = 'TR'
WHERE s2.name IS NULL
AND s1.xtype = 'U'
ORDER BY s1.name

ISNUMERIC SQL Server Trouble

If you use the ISNUMERIC function to determine if a value is numeric you might be in for a surprise
Run the 3 lines of code below and you will see what I mean

DECLARE @S VARCHAR(50)
SET @S = CHAR(9) --- @S NOW CONTAINS THE TAB CHARACTER
SELECT ISNUMERIC(@S), ISNUMERIC(CHAR(9)),ISNUMERIC('1D2'),ISNUMERIC('D')

As you can see TAB is returned as numeric as well the value 1D2
A better way to test for this would be with LIKE and %[a-z]%
If you run the example below you will see that the select statement with the ISNUMERIC function or LIKE returns one row more than the statement with LIKE and ISNUMERIC combined

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'

--returns ^
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'

--returns 1d2
SELECT * FROM #foo
WHERE ISNUMERIC(Value) = 1

--returns correct result
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

Compare Tables With Tablediff

In SQL Server 2005 there is a new utility to compare the data in two tables these can be in different tables and different servers.
The utility is Tablediff

The location is Program Files\Microsoft SQL Server\90\COM\tablediff.exe

From BOL:

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.

Perform a fast comparison by only comparing row counts and schema.

Perform column-level comparisons.

Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

Log results to an output file or into a table in the destination database.

Look it up in Books On Line

Troubleshooting Performance Problems in SQL Server 2005

It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This article provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005
There is also a word document versionf for download
If you are a SQL Server 2005 developer/administrator then this is a must read

You will be introduced to new views such as sys.dm_exec_sql_text, sys.dm_exec_cached_plan, Sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats

Below is a short list of what is covered in the article

Resource Bottlenecks
Tools for resolving resource bottlenecks

CPU Bottlenecks
Excessive compilation and recompilation
Inefficient query plan
Intra-query parallelism
Poor cursor usage


Memory Bottlenecks
External physical memory pressure
External virtual memory pressure
Internal physical memory pressure
Caches and memory pressure
Ring buffers
Internal virtual memory pressure



Tempdb
Monitoring tempdb space
Troubleshooting space issues
User objects
Version store
Internal Objects
Excessive DDL and allocation operations
Resolution

Slow-Running Queries
Blocking
Identifying long blocks
Blocking per object with sys.dm_db_index_operational_stats
Overall performance effect of blocking using SQL waits
Monitoring index usage


Appendix A: DBCC MEMORYSTATUS Description
Appendix B: Blocking Scripts


Read the whole article here (Troubleshooting Performance Problems in SQL Server 2005)

Contact Me

If you want to contact me and let me know that you like this blog or that you hate it or if you have any kind of suggestion then run the code below in Query Analyzer/Managment Studio to get my email (to prevent spam)

SELECT CONVERT(VARCHAR(31),0x73716C736572766572636F646540676D61696C2E636F6D)

Top 5 SQL Server Posts for December 2005

Below are the top 5 posts according to Google Analytics for the month of December

1) Feature Pack for Microsoft SQL Server 2005
2) Fun With SQL Server Update Triggers
3) SQL Server 2005 Free E-Learning
4) Find all Primary and Foreign Keys In A Database
5) Pad Numbers By Using CASE, LEFT And CONVERT

SQL Server Site Geo Map For December

These are the countries where the bulk of the visitors for this blog are coming from. As you can see most of the visitors are from the US and Europe, Asia is in third place. When I look at the maps day by day I also see some visitors from Africa and the Middle East but you need more than one visitor per city in order for it to show up in the monthly map. I only started Google Analytics at the end of November To see the November geo map click here

Top SQL Server Google Searches For December

These are the top SQL Searches on this site for the month of December. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
CONCATENATION
dts append
general network error
"Remote Server" "Linked Server" MSSQL
TOP command in SQL2000
append
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of memory.
delete trigger

Happy New Year

Happy new year to all of you

Format SQL Server Money Data Type

Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that

Below is an example

DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0) --1322323.67 Rounded but not formatted

SELECT CONVERT(VARCHAR,@v,1) --1,322,323.67 Formatted with commas

SELECT CONVERT(VARCHAR,@v,2) --1322323.6666 No formatting

If you have a decimal field it doesn't work with the convert function. The work around is to cast it to money

DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57 Formatted with commas

How To Resolve A Deadlock

I stumbled upon this SQL Server technical bulletin and it gives one example of how to resolve a deadlock. Typical methods you can use to resolve deadlocks include:
Adding and dropping indexes.
Adding index hints.
Modifying the application to access resources in a similar pattern.
Removing activity from the transaction like triggers. By default, triggers are transactional.
Keeping transactions as short as possible.
I won't go into details here since you can read it all at the following link (SQL Server technical bulletin - How to resolve a deadlock)

Also check out the following deadlock links
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Troubleshooting Deadlocks
Lock Compatibility

And of course if you really want to know everything about locking I recommend the following book (in PDF format): Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking by Kalen Delaney (Inside SQL Server 2000)

SQL Server 2005 System Table Map PDF Download

A couple of days back I reported that the current issue of SQL Server Magazine includes a poster of all the SQL Server 2005 System Tables. Well Microsoft has made this poster available as a PDF download now. You can get it here (SQL Server 2005 System Table Map)

SQL Query Optimizations

Below are a couple of small SQL query optimization tips
I have include the execution plan pictures for some of the queries so that you can see the difference

Don’t use * but list the columns
SELECT pub_name,city FROM dbo.publishers instead of SELECT * FROM dbo.publishers
If you would have a covering index on the columns pub_name and city then the table would not be accessed at all and all the data would be returned from the index. This would also reduce the logical reads. You can use STATISTICS IO to find out how many logical reads you would have

SELECT pub_name,city FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

SELECT * FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.


Don’t use arithmetic operators on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE OrderID*3 = 33000
SELECT * FROM Orders WHERE OrderID = 33000/3




Don’t use functions on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE LEFT(CustomerID,1) ='V'
SELECT * FROM Orders WHERE CustomerID LIKE 'V%'




Don’t convert a datetime column in the where clause
SELECT * FROM Orders WHERE OrderDate = '1996-07-04'
SELECT * FROM Orders WHERE CONVERT(CHAR(10),OrderDate,120) = '1996-07-04'



As you can see the query is many time faster if you don't convert

Test SQL Server Login Permissions With SETUSER

Sometimes you get a request to create a login and you want to test the permissions before letting the user know that he can use the login. you just don't feel like login in and trying to run some SQL statements for every user.
With the SETUSER statement you can eliminate that. Here is some code that explains how to use it

--Create the user
EXEC sp_addlogin 'Albert', 'food', 'pubs'
EXEC sp_adduser 'Albert'

CREATE TABLE dbo.test(id INT identity,datefield DATETIME)
INSERT INTO test
SELECT GETDATE()


SELECT * FROM test -- as dbo

-- let's run the select as Albert
SETUSER 'Albert'
SELECT * FROM test -- as albert

Now you should get this error
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'test', database 'pubs', owner 'dbo'.

execute just the SETUSER statement and you can drop Albert since the user will be reset to the original user
SETUSER
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'

or close the query window and in another window execute the code below to drop Albert
You have to do this in another window since as Albert you don't have permissions to do this
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach

This book has been published it is 768 pages and published by Apress and you can order it from amazon
You can download chapter 6 here to get a feel for the book

Need to brush up on specific SQL Server tasks, procedures, or Transact-SQL commands? Not finding what you need from SQL Server books online? Or perhaps you just want to familiarize yourself with the practical application of new T-SQL–related features. SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach is an ideal book, whatever your level as a DBA or developer.
This “no-fluff” desk reference offers direct access to the information you need to get the job done. It covers basic T-SQL data manipulation, the use of stored procedures, triggers and UDFs, and advanced T-SQL techniques for database security and maintenance. It also provides hundreds of practical recipes that describe the utilities of features and functions, with a minimim of background theory.
Additionally, this book provides “how-to” answers to common SQL Server T-SQL questions, conceptual overviews, and highlights of new features introduced in SQL Server 2005. It also features concise T-SQL syntax examples, and you can use the book to prepare for a SQL Server-related job interview or certification test.

Below is the table of contents

CHAPTER 1 SELECT
CHAPTER 2 INSERT, UPDATE, DELETE
CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking
CHAPTER 4 Tables
CHAPTER 5 Indexes
CHAPTER 6 Full-Text Search
CHAPTER 7 Views
CHAPTER 8 SQL Server Functions
CHAPTER 9 Conditional Processing, Control-Of-Flow, and Cursors
CHAPTER 10 Stored Procedures
CHAPTER 11 User-Defined Functions and Types
CHAPTER 12 Triggers
CHAPTER 13 CLR Integration
CHAPTER 14 XML
CHAPTER 15 Web Services
CHAPTER 16 Error Handling
CHAPTER 17 Principals
CHAPTER 18 Securables and Permissions
CHAPTER 19 Encryption
CHAPTER 20 Service Broker
CHAPTER 21 Configuring and Viewing SQL Server Options
CHAPTER 22 Creating and Configuring Databases
CHAPTER 23 Database Integrity and Optimization
CHAPTER 24 Maintaining Database Objects and Object Dependencies
CHAPTER 25 Database Mirroring
CHAPTER 26 Database Snapshots
CHAPTER 27 Linked Servers and Distributed Queries
CHAPTER 28 Performance Tuning
CHAPTER 29 Backup and Recovery

SQL Server 2005 Free E-Learning

Whether you are interested in database administration, database development, or business intelligence, there is some free * E-Learning to help you get up to speed on the newest features of the software. The E-Learning courses, valued at $99 each, are an effective way to learn on your own schedule and feature hands-on virtual labs that provide an in-depth, online training experience.

Database Administrator
2936: Installing and Securing Microsoft SQL Server 2005
2937: Administering and Monitoring Microsoft SQL Server 2005
2938: Data Availability Features in Microsoft SQL Server 2005

Database Developer
2939: Programming Microsoft SQL Server 2005
2940: Building Services and Notifications Using Microsoft SQL Server 2005
2941: Creating the Data Access Tier Using Microsoft SQL Server 2005

Business Intelligence Developer
2942: New Features of Microsoft SQL Server 2005 Analysis Services
2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005 Integration Services
2944: Updating Your Reporting Skills to Microsoft SQL Server 2005 Reporting Services

* Microsoft E-Learning for SQL Server 2005 is free until November 1, 2006. Note that this is a limited time offer and Internet connection time charges may apply.

Fun With SQL Server Update Triggers

Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (field) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean


CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )


INSERT INTO TestTrigger
SELECT 'SQL',500.23


CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS

IF @@ROWCOUNT =0
RETURN

IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO

--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows


--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row


--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows

--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it

--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1

--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300

--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows

Keep Your Statistics Up To Date

I had a scheduled job that runs every hour and took about a minute to complete. Somehow for no good reason this job suddenly takes hours and hours to finish. There was nothing done to the server, no service packs or patches. I checked fragmentation with DBCC SHOWCONTIG and it looked fine. I checked for blocking by running sp_who2 and there was no blocking going on. Now I was puzzled, what could have caused this? The table is not huge, about 50000 rows. Then I decided to update the statistics by running UPDATE STATISTICS and the problem went away immediately. The strange part is that there was such a huge difference in excution time, if it was double or triple I would understand but this was hunderds times slower. I don't have auto update statistics enabled because I have huge tables and can not have SQL server running sp_updatestats in the middle of the day. I have added this one table to the statistics job that I currently have and will monitor if the stats are up to date

SQL Server 2005 System View Poster

If you have never bought an issue of SQL Server Magazine then this is the month to do it. The latest issue comes with a huge SQL Server 2005 System View Poster. This poster itself is worth the price, I will get myself another issue so that I can have a poster at work and one at home. The poster contains all the system views and is organized in the following sections.
Objects, Types and Indexes
Trace and Eventing
Linked Servers
Common Language Runtime
Partitioning
Databases and Storage
Execution Environment
Service Broker
Security
Transaction Information
Serveriwide Configurations
Server-wide Information