Do maintenance plans require SSIS?

To many, this is a non-issue, since SSIS is installed anyhow. But not everyody installs SSIS. I for instance prefer to keep my production systems as clean as possible and only have what I really need (a principle which is harder and harder to live after as years go by…). Also, not all Editions of SQL Server comes with SSIS.

I did a test some months ago for SQL Server 2005 with a recent sp and also on SQL Server 2008. SQL Server 2008 did require SSIS (I tested both without and with SSIS installed), where 2005 sp2 didn’t. I recently learned from Phil Brammer in MVP group that there has been progress. Here’s the run-down, you don’t need SSIS installed to execute maint plans:

SQL Server 2005 requires sp2.

SQL Server 2008 requires CU3 or sp1 (see http://support.microsoft.com/kb/961126/). I dodn’t test this combo (2008 sp1 without SSIS), but I have no reason to doubt that KB article.

Watch out for that autogrow bug

Under some circumstances, autogrow for database files can be set to some 12000 percent. I think this is limited to SQL Server 2005 and for databases upgraded from SQL Server 2000 (I didn’t bother to search – feel free to comment if you know). So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.

I wrote a proc that I schedule that check for out-of-bounds values in sys.database files. The proc generates a bunch of messages it prints (handy if you have as Agent job with output file) and also constructs an error message and does RAISERROR (handy if you implemented alerting, for instance according to http://karaszi.com/agent-alerts-management-pack).

I prefer to schedule below as Agent job and use Agent alerts to notify me if we do have db with autogrow out-of-whack. As always, don’t use code if you don’t understand it.

USE maint
GO

IF OBJECT_ID('check_autogrow_not_percent'IS NOT NULL DROP PROC check_autogrow_not_percent
GO

CREATE PROC check_autogrow_not_percent
AS
DECLARE 
 @db sysname
,@sql NVARCHAR(2000)
,@file_logical_name sysname
,@file_phyname NVARCHAR(260
,@growth VARCHAR(20)
,@did_exist bit
,@msg NVARCHAR(1800)
,@database_list NVARCHAR(1000)

SET @did_exist CAST(AS bit)
SET @database_list ''

--For each database
DECLARE dbs CURSOR FOR
 SELECT name FROM sys.databases
OPEN dbs
WHILE 1
BEGIN
  FETCH NEXT FROM dbs INTO @db
  IF @@FETCH_STATUS <> BREAK

  SET @sql 'DECLARE files CURSOR FOR
 SELECT CAST(growth AS varchar(20)), physical_name, name
 FROM ' QUOTENAME(@db) + '.sys.database_files
 WHERE is_percent_growth = 1
 AND growth > 20'
  EXEC(@sql)
  OPEN files
  WHILE 1
  BEGIN
    FETCH NEXT FROM files INTO @growth@file_phyname@file_logical_name
    IF @@FETCH_STATUS <> BREAK
    SET @did_exist CAST(AS bit)
    SET @database_list @database_list '["' @db '": "' @file_logical_name '"]' CHAR(13) + CHAR(10)
   SET @msg 'Out-of-band autogrow in database "' @db '"' 
   ' with growth of ' @growth +
   ', logical file name "' @file_logical_name '"' 
   ', physical file name "' @file_phyname '"' 
   '.'
   RAISERROR(@msg101WITH NOWAIT
  END
  CLOSE files
  DEALLOCATE files
END
CLOSE dbs
DEALLOCATE dbs
IF @did_exist CAST(AS bit)
  BEGIN
   SET @msg 'Databases with out-of-control autogrow in databases: ' CHAR(13) + CHAR(10) + @database_list
   RAISERROR(@msg161WITH LOG
  END
GO

Spooky: What do you connect to?

I only recently discovered that SSMS will connect to different things. For instance, press the “New query” button. What were you connected to? The answer is the same server as your “current” server. But what is the current server? It is the server where you happened to have focus when the pressed the “New query” button. So, can you say whether you had focus in a query window, Object Exporer or Registered Servers?

This also applies to when you double-click a .sql file. And it doesn’t stop there. Open the “Registered Servers” window. Now, click on a server group. Go to explorer and double-click a .sql file. What were you connected to? Yes, all the servers in that group. Now, don’t get me wrong here; the ability to open the same query window against several servers can be a very useful thing. What I had no idea until just about now is how easily thsi can happen by mistake. Just by cklicking the New Query window, or even double-clicking an .sql file. So – be aware…

(FYI: SSMS 2005 doesn’t seem to do this for clicking a file in explorer, and the functionality to have a query window against several server didn’t exist in SSMS 2005…)

Table restore and filegroups

The story usually goes something like:

Q – How can I restore only this table?
A – Put it on its own filegroup and you can do filegroup level backup and restore.

The problem with above answer is that it most likely misses the point. We need to ask ourselves:
Why do you want to do a table level restore?

The answer to the question is very often that the table need to be reverted to an earlier point in time, possibly because some accident happened; like deleting all rows in the table by mistake. (See my minimizing data loss when accidents happens article for a more general discussion.) So, why is not filegroup backup that usable for this scenario?

SQL Server will not let you into a database where different data is from different points in time!
(2005+, Enterprise Edition and Developer Edition, has online restore which allow you into the database but you wont be able to access the restored data until you make it current – so it doesn’t really changes the basic issue here.)

Now, think about above. If we restore the filegroup backup containing the emptied table, but then need to restore all subsequent log backups up to “now”, what good did this song-and-dance-act do us? No good at all (except for a learning experience, of course).
We can of course restore the primary filegroup and the one with the damaged data into a new temp database – to the desired earlier point in time, and then copy the relevant data from this temp database into the production database. But this operation is certainly not as straight forward as just restoring the filegroup backup into the production/source database.

Now, about having data from different point in time (regardless of how you achieve it): Handle with care. Just think about relationship and dependencies you have inside a database. Reverting some table to an earlier point in time can cause havoc for those dependencies.

I won’t get into details about how filegroup backups work, online restore, the PARTIAL option of the restore command etc. – you can read about all that in Books Online. The point about this blog is to have somewhere I can point to when I see the “put-the-table-on-its-own-filegroup-and-backup-that-filegroup” recommendation.

As usual, I have a TSQL script to display my points. If you happen to think that it *is* possible to restore part of the database to an earlier point in time into the production/source database – I’m all ears. You can post a comment here, I will be notified. Please use below script as a template, and modify so that we can execute it and re-execute it.
The usual disclaimer is to not execute below if you don’t understand what it is doing, etc.

 

--Drop and create the database
USE master
IF DB_ID('fgr'IS NOT NULL DROP DATABASE fgr
GO
--Three filegroups
CREATE DATABASE fgr ON  PRIMARY
NAME N'fgr'FILENAME 'C:\fgr.mdf'),
FILEGROUP fg1
NAME N'fg1'FILENAME 'C:\fg1.ndf'),
FILEGROUP fg2
NAME N'fg2'FILENAME 'C:\fg2.ndf')
LOG ON
NAME N'fgr_log'FILENAME 'C:\fgr_log.ldf')
GO
ALTER DATABASE fgr SET RECOVERY FULL

--Base backup
BACKUP DATABASE fgr TO DISK = 'C:\fgr.bak' WITH INIT
GO

--One table on each filegroup
CREATE TABLE fgr..t_primary(c1 INTON "PRIMARY"
CREATE TABLE fgr..t_fg1(c1 INTON fg1
CREATE TABLE fgr..t_fg2(c1 INTON fg2

--Insert data into each table
INSERT INTO fgr..t_primary(c1VALUES(1)
INSERT INTO fgr..t_fg1(c1VALUES(1)
INSERT INTO fgr..t_fg2(c1VALUES(1)

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH INIT --1

--Filegroup backup of fg2
BACKUP DATABASE fgr FILEGROUP 'fg2' TO DISK = 'C:\fgr_fg2.bak' WITH INIT

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --2

--Delete from t_fg2
--Ths is our accident which we want to rollback!!!
DELETE FROM fgr..t_fg2

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --3

--Now, try to restore that filegroup to previos point in time
RESTORE DATABASE fgr FILEGROUP 'fg2' FROM DISK = 'C:\fgr_fg2.bak'
GO

SELECT FROM fgr..t_fg2 --error 8653
GO

--If we are on 2005+ and EE or Dev Ed, the restore can be online
--This means that rest of the database is accessible during the restore
INSERT INTO fgr..t_fg1(c1VALUES(2)
SELECT FROM fgr..t_fg1

--We must restore *all* log backups since that db backup
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = --out of 3
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = --out of 3
GO

SELECT FROM fgr..t_fg2 --Success
--We didn't get to the data before the accidental DELETE!
GO

----------------------------------------------------------------------------
--What we can do is restore into a new database instead,
--to an earlier point in time.
--We need the PRIMARY filegroup and whatever more we want to access
----------------------------------------------------------------------------
IF DB_ID('fgr_tmp'IS NOT NULL DROP DATABASE fgr_tmp
GO
RESTORE DATABASE fgr_tmp FILEGROUP 'PRIMARY' FROM DISK = 'C:\fgr.bak'
WITH
MOVE 'fgr' TO 'C:\fgr_tmp.mdf'
,MOVE 'fg2' TO 'C:\fg2_tmp.ndf'
,MOVE 'fgr_log' TO 'C:\fgr_tmp_log.ldf'
,PARTIALNORECOVERY

RESTORE DATABASE fgr_tmp FILEGROUP 'fg2' FROM DISK = 'C:\fgr_fg2.bak'

RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 1NORECOVERY
RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 2RECOVERY

--Now the data in PRIMARY and fg2 is accessible
SELECT FROM fgr_tmp..t_fg2

--We can use above to import to our production db:
INSERT INTO fgr..t_fg2(c1)
SELECT c1 FROM fgr_tmp..t_fg2

--And now the data is there again :-)
SELECT FROM fgr..t_fg2

Key count in sys[.]indexes

The old sysindexes table (as of 2005 implemented as a compatibility view) has a useful column named keycnt. This is supposed to give us the number of columns (keys) in the index. However, to make heads and tails out of the numbers, we need to understand how a non-clustered index is constructed. For a heap, the pointer to a row is the physical file/page/row address (aka “rowid”). This is counted as a key in the keycnt column:

IF OBJECT_ID('t1'IS NOT NULL DROP TABLE t1
GO
CREATE TABLE T1 (c1 INTc2 datetimec3 VARCHAR(3))
CREATE INDEX ix_T1_c1 ON T1 (c1)
CREATE INDEX ix_T1_c1_c2 ON T1 (c1c2)
CREATE INDEX ix_T1_c1_c2_c3 ON T1 (c1c2c3)
CREATE INDEX ix_T1_c2 ON T1 (c2)
SELECT namekeycntindidid
FROM sys.sysindexes
WHERE id OBJECT_ID('T1')

For the index on column (c2), you see a keycnt of 2. This is the key in the index plus the rowid.

For a nonclustered index on a clustered table, the row locator is the clustering key. Note, though, that if the clustered index is not defined as unique (PK, UQ etc), then another “uniqueifier” key/column is added. Building on above example:

CREATE UNIQUE CLUSTERED INDEX ix_T1_c1 ON T1 (c1WITH DROP_EXISTING
SELECT namekeycntindidid
FROM sys.sysindexes
WHERE id OBJECT_ID('T1')
GO
CREATE CLUSTERED INDEX ix_T1_c1 ON T1 (c1WITH DROP_EXISTING
SELECT namekeycntindidid
FROM sys.sysindexes
WHERE id OBJECT_ID('T1')

Consider the (non-clustered) index on column c2. For the first one, where the table has a unique clustered index, we see a keycnt of 2, the column c2 plus the clustered key. But when we define the clustered index as non-unique, we see +1 for the keycnt column; the uniqueifier. The uniqueifier is 4 byte, and only populated for rows which are duplicates of an existing clustered key (i.e. no extra cost if no duplicates).

But we want to stay away from compatibility views, right? Since we no longer have a key count column in sys.indexes, we need to grab that from sys.index_columns. This do not, however include the internal columns, only the explicitly defined columns:

SELECT
i.name
,i.index_id
,(SELECT COUNT(*)
FROM sys.index_columns AS ic
WHERE i.OBJECT_ID ic.OBJECT_ID
AND i.index_id ic.index_idAS keycnt
FROM sys.indexes AS i
WHERE OBJECT_ID OBJECT_ID('T1')

Remove transaction log files

Say you happened to get too many transaction log (ldf) files. Can you remove log files from the database? Yes, but only if a file isn’t in use, and you cannot remove the first (“primary”) log file.

So, be prepared to investigate the virtual file layout, using DBCC LOGINFO, to see if a log file is in use or not. You can find information about how to investigate the virtual log file layout in my shrink article. The basic steps are a bit similar to shrinking a log file: Investigate virtual log file layout, backup log, possibly shrink file, try removing it. Do this again as many times as it takes (repeat, rinse and lather).

Below is a script that, if you take the time to study it and play with it, will prepare you to remove transaction log files from a database. As always, don’t execute it if you don’t understand what it does!

USE master
IF DB_ID('rDb'IS NOT NULL DROP DATABASE rDb
GO

CREATE DATABASE rDb
ON
PRIMARY
NAME N'rDb'FILENAME N'C:\rDb.mdf' SIZE 50MB FILEGROWTH 1024KB )
LOG ON
(NAME N'rDb_log2'FILENAME N'C:\rDb_log2.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log3'FILENAME N'C:\rDb_log3.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log4'FILENAME N'C:\rDb_log4.ldf'SIZE 3MBFILEGROWTH 2MB)
GO

ALTER DATABASE rDb SET RECOVERY FULL
BACKUP DATABASE rDb TO DISK = 'C:\rDb.bak' WITH INIT
CREATE TABLE rDb..t(c1 INT IDENTITYc2 CHAR(100))

INSERT INTO rDb..t
SELECT TOP(15000'hello'
FROM syscolumns AS a
CROSS JOIN syscolumns AS b

--Log is now about 46% full
DBCC SQLPERF(logspace)

--Check virtual log file layout
DBCC LOGINFO(rDb)
--See that file 4 isn't used at all (Status = 0 for all 4's rows)

--We can remove file 4, it isn't used
ALTER DATABASE rDb REMOVE FILE rDb_log4

--Check virtual log file layout
DBCC LOGINFO(rDb)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--What if we backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is still in use (status = 2)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Shrink 3
USE rDb
DBCC SHRINKFILE(rDb_log3)
USE master

--... and backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is no longer in use

--Can now remove 3 since it is not in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Check explorer, we're down to 1 log file

--See what sys.database_files say?
SELECT FROM rDb.sys.database_files
--Seems physical file is gone, but SQL Server consider the file offline

--Backup log does it:
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'
SELECT FROM rDb.sys.database_files

--Can never remove the first ("primary") log file
ALTER DATABASE rDb REMOVE FILE rDb_log2
--Note error message from above

Response time vs. resource consumption

I see a trend towards more and more focusing on response time; and less and less on resource usage (resource consumption). I’ve even seen blanket statements such as the only thing that matters is response time. I do not agree. I feel that by being a good citizen and consume as few resources and possible, we contribute to the overall welfare of the system.

For instance, I’m fortunate to have some 8 km (5 miles) to my main client. I can take the car, which often is about 15 minutes or I can bicycle, which is about 20 minutes. For many reasons, I prefer to bicycle. The thing here is that I compromise a little bit and accept a few more minutes when going by bicycle, but I feel I’m a better citizen and contribute to a more sustainable society. But not only that: ever so often, the traffic is congested, and now the car takes some 40-45 minutes (bicycle still 20 minutes). By using the bicycle I both consume less resources and I also have a higher degree of predictability. Now, is this analogy appropriate to database performance? I don’t know, perhaps to some extent… But let me give you a database example, from real life, followed by a TSQL example:

I have a client who had this query which used to be quick (enough) and suddenly was very slow. Been there before, we know this can be just about anything. Anyhow, it was pretty quick for me to find the reason. The query had an ORDER BY and a FAST hint. The FAST hint tells SQL Server to return rows to the client as fast as possible, but possibly with a higher overall cost. The developer who added that FAST hint didn’t really think that hard about it, and just “threw it in there”. It sounds good, doesn’t it? There was a non-clustered index (non-covering) on the sort column and also some restrictions (WHERE clause).

With the FAST hint, SQL Server used the index on the sort column to drive the query and for each row it did a “bookmark lookup” to fetch the row. This means a page access for each row, but rows can be returned to the client application immediately (think streaming). Without the fast hint, SQL Server first sorted the relevant rows into a worktable and then returned the rows in sorted order from that worktable. So we have a tradeoff between reading a lot of pages (possibly some from cache) or doing some work up-front to sort data and then just read that sorted worktable sequentially.

The worrying part here is that with a small table, it will fit in cache and the difference between the two might not be that drastic (either way). But as table grew larger, it won’t fit in cache anymore and as we see logical I/O turning into physical I/O things really go south for the query with the FAST hint. This is what happened to my client. Table grew and a query which had OK response time suddenly was a disaster. If that FAST hint wasn’t there in the first place, my client wouldn’t have this slowness in the application over the two weeks it took until I had time to look over it and remove the FAST hint (I also added a couple of indexes, but that is beside the point).

Seeing is believing, right? At the end of this blog post, you will find TSQL that pretty much mimics my client’s case. It populates a table with 800,000 rows and there’s a non-clustered index on the sort column. We then try some variations to check response time, CPU seconds usage, I/O and duration. I measured response time using TSQL (as seen in the script). I also measured response time and the other metrics using Profiler.

The size of the table (clustered index on identity column) is 133MB and the non-clustered index to on the sort column is 11MB. This is a small table, but that makes things more manageable; and by setting the max server memory to a low value (60MB), we can still see the effect of logical vs. physical I/O.

We first run the query and have a filter that restricts to 4,000 rows out of 800,000 rows. Note that there’s no index on the filter column.

  • The query without a FAST hint was very consistent. The response time was either 0.05 seconds (without clearing cache first) or 1.9 seconds (if we clear cache first). This was regardless of if we configured with 500MB or 50MB memory for sp_configure ‘max server memory’.
  • The query with FAST hint was OK with memory setting of 500MB, so the table would fit in cache: 1.6 seconds to 4.4 seconds (depending on whether we empty cache before execution). But when we lower memory setting (think “large table”), the execution time jumped up to 73 seconds. That is a factor of between 48 and 1460.

Things got a bit different when we removed the WHERE clause to return all rows:

  • Query without FAST hint took between 10 seconds and 23 seconds (depending on whether we empty cache first) for a memory setting of 500MB. Lowering memory to 60MB made this one take between 23 and 41 seconds. Note that I here got some error messages from SQL Server regarding insufficient memory in the internal memory pool (possibly SQL Server now did some fall-back strategy for the query execution, which added to execution time).
  • The query with the FAST hint outperformed the one without for a memory setting of 500MB, with execution time between 2.2 and 5.6 seconds. Note that I configured SSMS to discard results so there is no processing of the returned 800,000 rows included here. With a memory setting of 60MB, we again bumped up execution time to some 74 seconds.

Here are the full numbers:

ms ms Profiler ms cpu io fast hint memconf cache clean rows returned
1930 2023 202 18782 0 500 1 4000
53 60 110 18768 0 500 0 4000
4403 4497 2075 2695310 1 500 1 4000
1616 1622 1622 2551439 1 500 0 4000
1930 1977 171 18768 0 60 1 4000
56 59 94 18768 0 60 0 4000
72426 72479 10888 5513944 1 60 1 4000
72663 72728 10983 5521626 1 60 0 4000
23336 23391 2105 31738 0 500 1 800000
10263 10269 2559 31574 0 500 0 800000
5663 5703 2386 2695368 1 500 1 800000
2226 2235 2028 2551439 1 500 0 800000
40966 40975 2620 31654 0 60 1 800000
22906 22913 2714 31629 0 60 0 800000
73676 73709 11045 5512080 1 60 1 800000
74513 74557 11778 5522556 1 60 0 800000

 

For the sake of completeness, I should add that having a good supporting index for the restriction (for the queries that had a restriction) made the query go equally fast regardless of memory config or FAST hint (in fact the FAST hint was irrelevant with a good index).

Here’s the T-SQL if you want to play with it. As always, don’t execute anything if you don’t understand the code and the consequences of executing it!

EXEC sp_configure 'max server memory'500
RECONFIGURE
GO

USE master
GO
IF DB_ID('TestDb'IS NOT NULL DROP DATABASE TestDb
GO
CREATE DATABASE [TestDb]
ON
PRIMARY
(NAME N'TDb'FILENAME N'C:\TDb.mdf'
,SIZE100MBMAXSIZE 200MBFILEGROWTH 30MB )
LOG ON
(NAME N'TDb_l'FILENAME N'C:\TDb_l.ldf'
,SIZE 200MBMAXSIZE 500MBFILEGROWTH 20MB )
GO

USE testDb

CREATE TABLE t(c1 INT IDENTITY PRIMARY KEY CLUSTEREDc2 INTc3 INTfiller CHAR(150))

INSERT INTO (c2c3filler)
SELECT TOP(80000011'hi'
FROM sys.columns AS a
CROSS JOIN sys.columns AS b
CROSS JOIN sys.columns AS c

UPDATE SET c2 c1 20c3 c1 200

CREATE NONCLUSTERED INDEX ON t(c2)

--Size of table and indexes
EXEC sp_indexinfo t
--Can be found at http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

IF OBJECT_ID('tmp'IS NOT NULL DROP TABLE tmp
GO
CREATE TABLE tmp
(seq tinyint IDENTITY(1,1PRIMARY KEY NOT NULL
,
ms INT NOT NULL
,
ms_profiler INT NULL
,
ms_cpu INT NULL
,
io_ INT NULL
,
fast_hint bit NOT NULL
,
memconf smallint NOT NULL
,
cache_clean bit NOT NULL
,
rows_returned INT NOT NULL)
GO

-----------------------------------------------------------------------------------------
--Create procedures
IF OBJECT_ID('emptyCache'IS NOT NULL DROP PROC emptyCache
GO
CREATE PROC emptyCache AS
BEGIN
CHECKPOINT
DBCC 
DROPCLEANBUFFERS
END
GO

IF OBJECT_ID('do_it'IS NOT NULL DROP PROC do_it
GO
CREATE PROC do_it
@fast_hint bit@memconf smallint@cacheclean bit@rows_returned INT
WITH 
RECOMPILE
AS
BEGIN
DECLARE 
@dt datetime SET @dt GETDATE()

IF @fast_hint CAST(AS bit)
IF @rows_returned 4000
SELECT FROM WHERE c3 16 ORDER BY c2
ELSE --return all rows
SELECT FROM ORDER BY c2
ELSE --add FAST hint
IF @rows_returned 4000
SELECT FROM WHERE c3 16 ORDER BY c2 OPTION(FAST 20)
ELSE --return all rows
SELECT FROM ORDER BY c2 OPTION(FAST 20)

INSERT INTO tmp(msfast_hintmemconfcache_cleanrows_returned)
VALUES(DATEDIFF(ms@dtGETDATE()), @fast_hint@memconf@cacheclean@rows_returned)
END
GO

TRUNCATE TABLE tmp
-----------------------------------------------------------------------------------------
--Return 4000 rows
-----------------------------------------------------------------------------------------

--500 MB memory
EXEC sp_configure 'max server memory'500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 0@rows_returned 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 0@rows_returned 4000
GO

--50 MB memory
EXEC sp_configure 'max server memory'60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 0@rows_returned 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 0@rows_returned 4000
GO

------------------------------------------------------------------------------------
--Return all 800,000 rows
------------------------------------------------------------------------------------

--500 MB memory
EXEC sp_configure 'max server memory'500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 0@rows_returned 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 1@rows_returneed 800000
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 0@rows_returned 800000
GO

--50 MB memory
EXEC sp_configure 'max server memory'60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 0@rows_returned 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 0@rows_returned 800000
GO

 

Management Pack for SQL Agent Alerts

I have finished my article about suggested SQL Server Agent Alerts. Perhaps calling this a “Management Pack” is OTT, but hopefully it will be useful to some of you folks.

We are talking about having Agent sending email to us when SQL Server writes messages to EventLog. The functionality has been in the product since version 6.0, but I have always lacked some good “default setting”. I’m talking about some easy way to tell Agent to notify me for messages which I typically want to be notified for. (Many of you are already using various Management software that has this functionality, so this won’t be for you 🙂 ).

I very much welcome feedback, you find all information in the article:

http://karaszi.com/agent-alerts-management-pack

 

sp_altermessage is back in business!

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again. I.e., we can change the is_event_logged column in sys.messages. This is very valuable in general and specifically is you want to define Agent alerts (for which Agent polls the Eventlog). For instance:

SELECT * FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033

Notice the value for the is_event_logged column. Now, run below:

EXEC sp_altermessage
 @message_id = 1205
,@parameter = ‘WITH_LOG’
,@parameter_value = ‘true’

Now, re-run the select statement and see that you modified the behavior for the system message. Now, re-run the sp_altermessage with ‘false’ to reset to default.

The ability to modify this behavior for system messages was available prior to SQL Server 2005, but some re-architecturing in 2005 removed the functionality. kozloski informed me in this blog post that 2005 sp3 re-introduced the functionality and obviously as of sp1 the functionlity is back in 2008 as well.

 

Match those types!

This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the column side – not the literal side of your query. What does that mean? Consider below:

WHERE Col = Val

Now, say that the types for above don’t match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren’t of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for “Data Type Precedence“.

What we don’t want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here’s the repro script from that thread (slightly adjusted by me):

USE tempdb
GO
IF OBJECT_ID('t'IS NOT NULL DROP TABLE t
GO
CREATE TABLE t
(
c1 int IDENTITY(10000001,1) NOT NULL
,
c2 char(8) NULL
,
c3 datetime NULL
)
GO
INSERT INTO t(c3)
SELECT TOP 3000000 '20080203'
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
UPDATE SET c2 CAST(c1 AS char(8))
CREATE UNIQUE CLUSTERED INDEX ON t(c2)
GO

SELECT FROM WHERE c2 N'10000009'
SELECT FROM WHERE c2 N'10000008'

Don’t bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.

Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find “WHERE:(Convert([t].[c2])=[@1])”. See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That’s just because we were a bit sloppy… Compare to below:

SELECT FROM WHERE c2 '10000009'
SELECT FROM WHERE c2 '10000008'

You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.

OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn’t see any difference between the two original queries (N’10000009′ and N’10000008′). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the “9” query was slower than the “8” query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the “8” version vs. the “9” version. I used Profiler and confirmed. The “9” version had consistently about 90000 microsecond duration where the “8” version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn’t give me anything. Perhaps I didn’t capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. 🙂

My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N’10000009′ compared to N’10000008′. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like “Constant Scan” and “GetRangeThroughConvert” for other blog post on this topic.)

The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!