Deferred Name Resolution, or “Why does my proc allow invalid table names?”

A developer came to me a few days ago, wondering why when he specifies an invalid table name in a stored procedure, the proc saves to the database, but when he specifies an invalid column name it fails.

Looks like the deferred name resolution only works w/ missing tables…not w/ missing columns: http://msdn.microsoft.com/en-us/library/ms190686.aspx

For instance, this succeeds on an DB:

create proc cp_test
as
select somecolumn from tFakeTable –fake column, fake table

This fails on an DB:

create proc cp_test2
as
select somecolumn from tActualTable–fake column, real table

Analyzing Performance Monitor Data With SQL Server Profiler

A really cool feature in SQL Server Profiler is the ability to import performance monitor (perfmon.exe) logs.  Prior to this, DBAs used to have to jump through hoops working w/ tables, excel, and whatever else we had on hand to determine exactly what database activities are causing the resource spikes.

1) Create a Counter Log.   Add only relevant counters, and remember that there is a resource overhead to any type of monitoring activity.  Also remember to set the interval to a number that will generate a manageable, but useful amount of data.

2)  Start a Profiler trace with a time span that overlaps that of the Counter Log.  Again, trace only those events that are relevant, to keep overhead as light as possible (Management Studio –> Tools Menu –> SQL Server Profiler, then File –> New Trace).

3) After your  Counter Log and Profiler trace have accumulated enough data for your needs (a single query’s worth, or a month’s worth, depending on your goal), stop both and make sure they are saved.  Then go to Profiler, File–> Import Performance Data…

You can filter down both your Perfmon counters and your SQL Server columns, to pare down what you are analyzing.  Also, we can highlight a certain range on the graph to localize the trace to that time span.

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO

SQL UPSERT

What is an UPSERT?

An UPSERT is a technique to perform inserts/updates in a database using the least amount of database reads. For maximum performance, you want to absolutely minimize the number of reads required of the database.

An UPSERT is really just an UPDATE combined with an INSERT. The classic example, is if you are writing a stored proc to look for a row and update it; if it does not exist, then insert. Most programmers will use an IF, EXISTS syntax to accomplish this. This technique will perform two I/O operations regardless of the existence of the row: one to see if the row exists, and a second to either update or insert. A basic UPSERT syntax is:
———————————–
UPDATE dbo.tObject
SET objectStatusCode = ‘Active’
WHERE objectNumber = 100

IF @@ROWCOUNT = 0

INSERT INTO dbo.tObject
(objectNumber,
objectName)
VALUES
(100,
‘Test Object’)
GO
–You can run the following before and after the statement to view I/O stats:
SET STATISTICS IO ON
–Statement here
SET STATISTICS IO OFF

———————————–

If an INSERT is required, then we will have 2 I/O operations, but if only an UPDATE is required, it would only be 1 I/O operation. The alternative with an IF, EXISTS syntax is to always have 2 I/O’s.

Indexes – to Rebuild or Reorganize?

Index maintenance is one of the best “bang for the buck” performance tuning activities a DBA can perform. Due to factors such as page splits and updates to index and branch leaf pages, index become fragmented, resulting the in index data no longer being physically contiguous. Looking at our available options as far as index maintenance goes, there may be some confusion as whether we should be rebuilding our indexes, or performing an index reorganization. It really depends on their degree of fragmentation.

Microsoft recommends rebuilding if fragmentation is over 30%, reorganizing if it’s between 5% and 30% and leaving it alone if fragmentation is below 5%. In SQL Server 2005, you can use the sys.dm_db_index_physical_stats function to view the fragmentation of an index.

This will get detail for all tables in your DB, returning the three key fields first and ordering by most fragmented:

—————————————————————————————-

SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
dm.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
AND i.index_id = dm.index_id
order by avg_fragmentation_in_percent desc

—————————————————————————————-

To reorganize: ALTER INDEX REORGANIZE –reorgs and repacks the leaf pages of the index; this makes DBCC INDEX DEFRAG obsolete.

To rebuild: ALTER INDEX REBUILD WITH (ONLINE = ON) –drops the index and recreates it; this makes
DBCC DBREINDEX and DBCC INDEX REBUILD obsolete.

Rebuild syntax: ALTER INDEX PKtTable1
ON dbo.tTable1
REBUILD
GO

The (ONLINE = ON) operator allows the index to be rebuilt while the underlying data is accessed by other users (new to SQL 2005).

The following script will rebuild indexes for all tables in your database:

—————————————————————————————-

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name = ‘YourDatabaseName’
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

– create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

– SQL 2000 command
–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)

– SQL 2005 command
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

—————————————————————————————-

Now, you may notice that even after rebuilding your indexes, many still show a large percentage of fragmentation. Hint: check out the page_count column of sys.dm_db_index_physical_stats. If it is a small number, SQL Server may have just skipped over this index. This is fine, as fragmentation in small tables/indexes has a minimal hindrance on performance.