|
|
Tuesday, April 8, 2008
The SQL Server BOL lacks the syntax for renaming constraints, and most of the code samples on the internet flat out don't work. By trial and error, I discovered the correct syntax:
CREATE TABLE foo (
bar INT,
CONSTRAINT foo_unique_bar UNIQUE (bar)
)
EXEC sp_rename 'foo_unique_bar', 'foo_unique_bar2', 'object'
Note that you do not use the table's name in the first parameter to sp_rename. This is contrary to how sp_rename usually works, so it's an effective monkey wrench.Labels: SQL Server, SQL Server - Scripts
Permalink  
Monday, February 11, 2008
Recently, our tempdb log went completely bonkers, growing from its normal 30 GB to fill its 68 GB drive (with all the subsequent freakings-out by SQL Server and required service bouncings which you would anticipate). While glaring at job durations and recently-released builds, I threw a couple of small jobs into SQL Server to monitor tempdb data and log usage so it could page me if it decided to grow the tempdb log excessively and so that I could see when growth started and what it might correlate to.
I made two small tables and a sproc to fill each and scheduled the sprocs to run every 10 minutes. I found two interesting things.
- Our tempdb data files could be much smaller. They're currently 20 GB, and we have 8 of them, as we're using the T1118 tempdb concurrency enhancement at the direction of Microsoft (it's ostensibly for 2000 only, but we're running 2005 and they have us using it), and they top out at 1.8 GB of data each.
- The tempdb checkpoint interval, which as far as I can tell I have no control over, as it has nothing to do with the server-wide recovery interval, appears to be 2-3 hours during the week and 6-7 hours on the weekend. When I graphed the log usage in Excel, the amount of data in the log very clearly repeatedly grew steadily to a point (13 GB during the week, 19 GB on the weekend), then plummeted to 0, then grew steadily back to that same point.
My tables for recording sizes:
CREATE TABLE dbo.tempdbDataSize (
time SMALLDATETIME NOT NULL DEFAULT GETDATE(),
filename CHAR(8) NOT NULL,
usedMb SMALLINT
)
CREATE TABLE dbo.tempdbLogSize (
time SMALLDATETIME NOT NULL DEFAULT GETDATE(),
gb TINYINT,
usedGb TINYINT
)
If you haven't changed your tempdb file names, you can use a CHAR(7) in tempdbDataSize. Since we have 8, I renamed the first one from "tempdev" to "tempdev1" for consistency and numbered them (tempdev1 - tempdev8). We have a busy system, so I'm recording my tempdb log sizes in gigabytes. You may need to use megabytes instead, in which case you'll want to kick those TINYINTs up to SMALLINTs and remove the division by 1024 from the statement (below) which audits the log usage.
I found the code to get the file usage by turning on SQL Profiler and then opening the Shrink Files pane in SSMS (remember not to click OK in this pane if you try this yourself -- shrinking tempdb while people are using it is Supremely Bad Idea).
The tempdb data size audit sproc:
CREATE PROCEDURE dbo.tempdbDataSize_audit AS
SET NOCOUNT ON
CREATE TABLE #tmpspc (
Fileid int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name sysname,
FileName nchar(520)
)
EXEC('USE tempdb INSERT INTO #tmpspc EXEC (''dbcc showfilestats'')')
INSERT INTO tempdbDataSize (filename, usedMB)
SELECT name, UsedExtents * 64 / 1024
FROM #tmpspc
DROP TABLE #tmpspc The tempdb log size audit sproc:
CREATE PROCEDURE dbo.tempdbLogSize_audit AS
SET NOCOUNT ON
DECLARE @sizeGb INT, @usedGb INT
CREATE TABLE #tmplogspc (
databaseName SYSNAME,
logSizeMB FLOAT,
SpaceUsedPercent FLOAT,
status BIT
)
INSERT INTO #tmplogspc
EXEC ('dbcc sqlperf(logspace)')
SELECT @sizeGb = logSizeMb / 1024, @usedGb = logSizeMB * spaceUsedPercent / 100 / 1024
FROM #tmplogspc
WHERE databaseName = 'tempdb'
DROP TABLE #tmplogspc
DECLARE @subject VARCHAR(50)
IF @sizeGb >= 40
BEGIN
SET @subject = 'Tempdb log size is ' + CAST(@sizeGb AS VARCHAR) + ' GB'
EXEC sendEmail @toEmails = 'me@mydomain.com', @subject = @subject
END
ELSE IF @sizeGB > (SELECT TOP 1 gb FROM tempdbLogSize ORDER BY time DESC)
BEGIN
SET @subject = 'Tempdb grew to ' + CAST(@sizeGb AS VARCHAR) + ' GB'
EXEC sendEmail @toEmails = 'me@mydomain.com', @subject = @subject
END
INSERT INTO tempdbLogSize (gb, usedGb) VALUES (@sizeGb, @usedGb) The sendEmail sproc is my custom wrapper for sp_send_dbmail -- you can just drop in a sp_send_dbmail call.
Diagnosis
My findings haven't been particularly useful in diagnosing the mysterious tempdb log growth. As a bandaid to prevent further system unavailability, I changed my sproc that rebuilds indexes overnight to do its sorts in the user database rather than in tempdb. This causes them to take a little longer, as the happy parallelism of having the index and the rebuild spread across multiple physical disks is lost, but the tempdb log has behaved since. One of the first things I checked for was recent large growth in table size (you record rowcounts daily so that you can see growth over time, right?), figuring this would correspond to large growth in index size and thus tempdb log usage during rebuilds, and there wasn't any, so this isn't particularly helpful.Labels: SQL Server, SQL Server - Diagnostics, SQL Server - Scripts
Permalink
Wednesday, January 2, 2008
The BOL rather unhelpfully doesn't give an example of how to pass multiple parameters to sp_executesql, leaving one to wonder whether it's
EXEC sp_executesql @sql, N'@start DATETIME', @start, N'@finish DATETIME', @finish
or possibly
EXEC sp_executesql @sql, N'@start DATETIME, @finish DATETIME', @start, @finish
(That N is important by the way -- the documentation also doesn't mention that the SQL statement and the parameter definitions must be NVARCHARs, not VARCHARs, and the error message that sp_executesql returns is only vaguely helpful.)
It turns out that #2 is the correct syntax, though you wouldn't know it from the intelligible error returned by sp_executesql. Thanks again, Google!Labels: SQL Server, SQL Server - Scripts
Permalink
Wednesday, November 21, 2007
If you need to return a single integer from a stored procedure, it's more efficient to use the RETURN statement than SELECT since SELECT is a whole recordset with a cursor. To access the return value from your C# code, you add a "ReturnValue" parameter to your stored procedure call:
(Pretend you have an open SqlConnection called "conn".)
SqlCommand sqlCommand = new SqlCommand("webmail_getMessageCount");
sqlCommand.Connection = conn;
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter messageCount = new SqlParameter("@count", SqlDbType.Int);
messageCount.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(messageCount);
sqlCommand.ExecuteNonQuery();
return (int)messageCount.Value;Labels: ASP.NET, SQL Server, SQL Server - Scripts
Permalink
Thursday, October 18, 2007
Out of memory? Use the sys.dm_exec_query_memory_grants DMV to find the heaviest query running right now and kill it.
By memory usage:
SELECT session_id, (requested_memory_kb/1024.) AS [Requested Memory (MB)], grant_time, query_cost, text
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY requested_memory_kb DESC
By query cost:
SELECT session_id, (requested_memory_kb/1024.) AS [Requested Memory (MB)], grant_time, query_cost, text
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY query_cost DESCLabels: SQL Server, SQL Server - Diagnostics, SQL Server - Scripts
Read the article.
Permalink
Friday, October 12, 2007
Microsoft's system sprocs are nice and all, but they're rather basic. I have written an improvement to sp_helpindex which also includes key columns, included columns, reads and writes, fragmentation, and freshness of statistics. Like sp_helpindex, it goes in master but can be called from any database. Download the script with proper tabbing.
USE master
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_betterhelpindex')
DROP PROCEDURE sp_betterhelpindex
GO
CREATE PROC dbo.sp_betterhelpindex @tableName VARCHAR(255) AS
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SET NOCOUNT ON
USE ' + DB_NAME() + '
DECLARE @object_id INT, @index_id INT, @indexName VARCHAR(500)
DECLARE @keyColumns VARCHAR(2000), @includedColumns VARCHAR(2000)
SET @object_id = OBJECT_ID(''' + @tableName + ''')
DECLARE @index TABLE (
index_id INT,
keyColumns VARCHAR(2000),
includedColumns VARCHAR(2000)
)
DECLARE sp_betterhelpindex_indexes CURSOR FAST_FORWARD FOR
SELECT index_id
FROM sys.indexes
WHERE object_id = @object_id
OPEN sp_betterhelpindex_indexes
FETCH sp_betterhelpindex_indexes INTO @index_id
WHILE @@fetch_status = 0
BEGIN
SELECT @keyColumns = NULL, @includedColumns = NULL
SELECT @keyColumns = ISNULL(@keyColumns + '', '', '''') +
columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
FROM caV2.sys.indexes indexes
INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
AND indexes.index_id = index_columns.index_id
INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
AND index_columns.column_id = columns.column_id
WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 0
ORDER BY index_column_id
SELECT @includedColumns = ISNULL(@includedColumns + '', '', '''') +
columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
FROM caV2.sys.indexes indexes
INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
AND indexes.index_id = index_columns.index_id
INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
AND index_columns.column_id = columns.column_id
WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 1
ORDER BY index_column_id
INSERT INTO @index VALUES (@index_id, @keyColumns, ISNULL(@includedColumns, ''''))
FETCH sp_betterhelpindex_indexes INTO @index_id
END
CLOSE sp_betterhelpindex_indexes
DEALLOCATE sp_betterhelpindex_indexes
SELECT sys.indexes.name,
(CASE WHEN is_primary_key = 1 THEN ''primary key, ''
WHEN is_unique = 1 THEN ''unique, ''
ELSE '''' END) + LOWER(sys.indexes.type_desc) AS type,
keyColumns, includedColumns,
sys.filegroups.name AS [filegroup],
user_seeks + user_scans + user_lookups AS readsByQueries, user_updates AS updates,
CAST(physicalStats.avg_fragmentation_in_percent AS INT) AS [fragmentation %],
STATS_DATE (@object_id , i.index_id) AS statisticsUpdated, i.index_id
FROM @index i
INNER JOIN sys.indexes ON sys.indexes.object_id = @object_id AND i.index_id = sys.indexes.index_id
INNER JOIN sys.filegroups ON sys.indexes.data_space_id = sys.filegroups.data_space_id
INNER JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = @object_id
AND i.index_id = usage.index_id
AND usage.database_id = DB_ID()
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), @object_id, NULL, NULL, NULL) physicalStats
ON i.index_id = physicalStats.index_id AND alloc_unit_type_desc != ''LOB_DATA''
ORDER BY sys.indexes.type_desc, keyColumns, includedColumns
'
EXEC(@sql)Labels: SQL Server, SQL Server - Scripts
Permalink
|
|
