|
|
Friday, October 16, 2009
While nosing in the Tuning options for SQL Server 2005 and SQL Server 2008 when running in high performance workloads article in Microsoft's Knowledge Base, I found the following flag available for Windows 2003 servers with at least 8 GB of RAM running SQL 2005 or above.
Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.
First, a better explanation of what the TLB is, how its efficiency can suffer, and how allocating large pages helps:
In the CPU, there's a translation table of pages to their locations in memory called the Translation Lookaside Buffer. If you have more pages than will fit in the table, only the most recent address are kept in the CPU, and the whole table is in elsewhere. Just like data served out of SQL Server's buffer pool is accessed much more quickly than data served off a disk, pages in the TLB are accessed much more quickly than if the address has to be retrieved out of the full table in main memory. The fewer memory pages you have, the more likely that they will all fit in the buffer in the CPU, avoiding those costly trips out to look at the full translation table. The 834 trace flag tells SQL Server to allocate larger pages for its buffer pool (2 MB to 16 MB, depending on your hardware, rather than 8 KB) so that there will be fewer of them.
Sounds intriguing! Would my high performance system benefit from this? The information out there is pretty slim, but here's what I dug up. - This Usenet posting by a SQL MVP suggests that you should only use it if your system is CPU-bound rather than IO-bound and your signal to resource wait times are high. To see if your signal to resource wait times are high, shimmy on over to the sys.dm_os_wait_stats DMV:
SELECT wait_type, signal_wait_time_ms / wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_type
SELECT SUM(signal_wait_time_ms) / SUM(wait_time_ms)
FROM sys.dm_os_wait_stats Mine are all zero, and my production cluster sees about 1500 batch requests per second during its peak use. - A member of the Microsoft SQL Server Performance Team specifically mentions not using the flag unless you're running your CPU above 90%. If you're doing that on your production system, it's way past time for new hardware, but I suppose you could try this first, provided that your signal-to-wait ratio is also high.
- Monitoring for translation lookaside buffer misses is also mentioned, but I can't find any way to do this on a Windows system. There are a few Intel Pentium 4 manuals and an O'Reilly system tuning book that mention the existence of the TLB, but there are no perfmon counters to monitor. The O'Reilly book does mention a tool for Solaris.
My conclusion is that this wouldn't benefit me. Check your CPU usage, I/O stalling, I/O waits and signal-to-resource ratios to see if it would help you. Labels: SQL Server, SQL Server - Performance
Permalink  
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
Tuesday, March 18, 2008
The March issue of SQL Server magazine has a blurb about a new SQL Server stress-testing tool called SqlQueryStress. It lets you plug in a query and (optionally) parameterize it from the results of another query. For example, a query which has a user ID as a parameter, and it will fill that parameter with values from a query that selects some subset of your users. Very cute.
Anyway, the fun part of this is that the network admin and I have been trying to devise ways to sent lots of traffic to a recalcitrant SQL Server which seems to be suffering from a TCP chimney bug with HP's NICs for which the fix is to turn off some TCP chimney setting. It randomly falls offline itself, so we want to reproduce the error, turn off the setting, then verify that we can no longer break it. We've been trying combinations of copying several large files and running queries, but without success, so today I installed SqlQueryStress on fifteen servers, set it to spawn 200 threads that each ran 24,000 queries (this would cause it to run for about an hour), and let it rip. Within three minutes, we had to shut them all down because we'd flooded the network with so much traffic that our secondary office building down the street could no longer use the internet.
Most fun I'd had during a day in the office in a long time!Labels: SQL Server
Permalink
The March issue of SQL Server magazine has a blurb about a new SQL Server stress-testing tool called SqlQueryStress. It lets you plug in a query and (optionally) parameterize it from the results of another query. For example, a query which has a user ID as a parameter, and it will fill that parameter with values from a query that selects some subset of your users. Very cute.
Anyway, the fun part of this is that the network admin and I have been trying to devise ways to sent lots of traffic to a recalcitrant SQL Server which seems to be suffering from a TCP chimney bug with HP's NICs for which the fix is to turn off some TCP chimney setting. It randomly falls offline itself, so we want to reproduce the error, turn off the setting, then verify that we can no longer break it. We've been trying combinations of copying several large files and running queries, but without success, so today I installed SqlQueryStress on fifteen servers, set it to spawn 200 threads that each ran 24,000 queries (this would cause it to run for about an hour), and let it rip. Within three minutes, we had to shut them all down because we'd flooded the network with so much traffic that our secondary office building down the street could no longer use the internet.
Most fun I'd had during a day in the office in a long time!Labels: SQL Server
Permalink
Monday, March 17, 2008
Summary: Stop SQL Server Agent before you enable service broker.
One of our production database clusters has developed a nasty habit of losing network connectivity with no error messages or indicators of why, necessitating 1am drives to the NOC by a network admin to growl at it and scramblings to put the system it backs into emergency maintenance mode. The second time this happened, the operations team quite sensibly requested that I move all the databases to another server so that they could diagnose this one.
We had just transitioned our other production cluster from a set of servers attached to an MSA 1000 to a new set of servers attached to an EVA 8100, and the MSA and old servers were still hanging around. It was a matter of a few hours to coordinate offline time (thankfully, this database cluster housed non-24-hour-available systems), and agitate at developers until they changed connection strings and deployed code.
All went smoothly except that my job monitor -- a job that runs every two hours and emails me about jobs which have failed or taken longer than usual in the meantime -- failed. When it went to send email, it complained that service broker needed to be enabled for the database (no mention of which database, but I assumed msdb). I nipped over the BOL, found the appropriate command, and ran it. Twenty minutes later, it was still running. I killed it and started it again. Same hanging. I hopefully ran the command in the database that houses the job monitor sproc. It finished immediately, but it didn't make mail work.
Eventually, it occurred to me that the SQL Server Agent, with its dirty little fingers constantly in msdb, could be causing a database configuration change to wait indefinitely for some sort of exclusive access. I stopped SQL Server Agent, ran the command to enable service broker, and it returned immediately. Yay!Labels: SQL Server
Permalink
Saturday, February 23, 2008
Or, "Further Lessons Learned from Today's Migration."
When you change servers and restore msdb, you may find your maintenance plans failing and putting the following messages into various logs:
- The SSIS subsystem failed to load
- Subsystem could not be loaded
- The job has been suspended
- The specified module could not be found
The problem is most likely that the location of your SQL Server installation directory differs from that of the old server. Ours happened to be on a different disk drive. SQL Server keeps a table of pointers to the SSIS DLLs in msdb, and you need to flush this table and repopulate it with the new locations (KB article 914171). This is very simple.
To verify that this is in fact the problem,
SELECT * FROM msdb.dbo.syssubsystems
If the directory pointed to by the rows doesn't exist, you've found the problem! Two quick queries will fix you right up:
-- Delete the existing rows.
DELETE FROM msdb.dbo.syssubsystems
-- Fill the table with new rows pointing to the proper location of the DLLs.
EXEC msdb.dbo.sp_verify_subsystems 1 And finally, you need to restart SQL Server Agent for it to pick up the new table entries and unsuspend the SSIS jobs.
One More ThingIf you've changed servers and have SSIS packages (like maintenance plans), there's another thing you most likely need to do. Each package/plan has at least one database connection, and they're probably pointing to the old server. You need to modify each package/plan individually, changing the existing connection if you can (I couldn't -- all of the input boxes were disabled) or creating a new connection and changing all of the steps to use it. I've never been a user of DTS packages -- too rickety and GUI-driven for me -- but I can see why no one likes the direction Microsoft went with turning maintenance plans into DTS packages in SQL Server 2005.Labels: SQL Server
Permalink
Or, "Lessons Learned from Today's Migration."
We migrated our production database cluster from an MSA 1000 to an EVA 8100 with new servers this morning. (This is much easier than in-place upgrades where you take down the system, wipe the cluster nodes and rebuild them on the spot if you can swing it.) It went fairly smoothly, though when I went to compare the amount of time the EVA took to make full backups of all the databases (8 minutes) to the time it took the MSA (21 minutes), I found that even though I'd restored msdb from the previous server, there was no job history. Apparently SQL Agent doesn't take kindly to foreign job history and zorched it as soon as the new server ran its first job. I restored a copy of msdb, shut down SQL Agent and sucked the contents of sysjobhistory back in from the copy, changing the server column to the new server's name (and having to bump up the value of instance_id since the new server's SQL Agent had already made a few records), and the transplant took.Labels: SQL Server
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
Monday, January 28, 2008
You might expect that you could add full-text search to an existing SQL Server instance by firing up the install media. You'd be wrong. You must go into the Control Panel, into Add/Remove programs, click the Microsoft SQL Server 2005 component, and click its Change button. Don't forget to re-apply your service packs!Labels: SQL Server
Permalink
Tuesday, January 22, 2008
If not otherwise specified, these numbers are from a Microsoft webcast.
SQL Server: Buffer Manager
- Page life expectancy should be > 300
- If Page lookup/sec / Batch Requests / sec is > 100, then you're doing too much I/O.
- Lazy writes/sec should be 0. > 20 is bad, indicates memory pressure.
- Free pages should be >= 640.
Memory manager
- Memory grants pending should be 0
Latches
- Avg Latch Wait Time(ms) should be less than 10
Locks
- Total wait time (ms) > 60,000 ms is bad.
SQL Server Statistics
- Compilations/sec should be less than 20% of batch requests.
- Recompiles should be less than 10% of batch requests.
Threads
Labels: SQL Server, SQL Server - Diagnostics
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
Wednesday, November 7, 2007
I use Microsoft's Log Parser to collect events that happened in the time surrounding job failures in case they contain helpful data. All the examples of doing this from within SQL Server require you to put the folder containing the Log Parser executable in the server's PATH environment variable, and you have to bounce SQL Server to get it pick up the change. When the operations team forgets to install it during the server configuration, you can't just bounce a production server in the middle of the day when you find out.
I wished therefore not to use an environment variable. I tried specifying the full path to the executable in my call to xp_cmdshell, with and without quotes. No dice. I finally hit upon using the DOS "short names" (the ancient 8.3 names from the bad old days of Windows), which works.
Presuming the default installation location (c:\Program Files\Log Parser 2.2), the DOS short path is
c:\PROGRA~1\LOGPAR~1.2\LogPar~1.exe
on an x86 server, and
c:\PROGRA~2\LOGPAR~1.2\LogPar~1.exe
on an x64 server (Progra~2 is the "Program Files (x86)" directory).Labels: SQL Server
Permalink
Monday, October 22, 2007
Microsoft's getting a little punchy!
An access violation occurs in the "sqlservr!BufferPartition::Steal" function, and SQL Server stops responding.
Committing an access violation is the definition of stealing, no? So this seems like perfectly normal behavior for a steal function!
I like that SQL Server appears to get mad and ignore you when you the buffer manager starts stealing. This confirms my suspicion that SQL Server is actually an obnoxious child.Labels: SQL Server, Titter
Read the article.
Permalink
Friday, October 19, 2007
My observations suggest that you can only add a maintenance cleanup task (to delete expired backups) from the machine on which SQL Server resides. When I run the maintenance plan wizard from SSMS on my own machine, I don't get that option.
Once you successfully add a maintenance cleanup task, how do you get it to delete .bak, .dif and .trn files all in the same task? Leaving the extension box blank doesn't result in anything getting deleted. The secret is to enter * as the extension. The period before the extension should be omitted.Labels: SQL Server, SQL Server - Backups, SQL Server - SSMS
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
Tuesday, October 16, 2007
Extracted from an article on DatabaseJournal.com.
Theoretically, SQL Server 2008 will be released in Q1 of 2008, along with a new version of Visual Studio and Windows.
Data Compression
According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved by reducing I/O and increasing buffer-hit rates. Data and indexes can both be compressed, and it doesn't affect other features.
Performance Data
More perf counters have been added. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.
Performance Dashboard: tool that can read saved performance data. Historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.
Performance Studio: a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several collecting methods, including queries, traces, and perf counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options, including suggestions for potential performance tuning.
Installation
The installer will be able to retrieve updates from Microsoft and install SQL Server, service packs, and patches as a single step. Service packs will be uninstallable.
Data Synchronizing Features
SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating disconnected applications which synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.
Dates and Times
New data types in SQL 2008.
- Date: date only, no time.
- Time: time only, no date.
- Date Time Offset: "UTC time-zone aware value" (whatever that means)
Table-Valued Parameters
It's sort of a hack, but you create a UDT which is a table and pass variables of that type into sprocs.
Merge
A new TSQL command, MERGE, allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.
MERGE InventoryMaster AS im
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON im.InventoryID = src.InventoryID
WHEN MATCHED THEN
UPDATE SET im.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src.InventoryID, src.Descr);Labels: SQL Server, SQL Server 2008
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
Wednesday, October 10, 2007
Suggests a possible cause for when you're debugging a sproc that is sometimes fast and sometimes slow, even after clearing the procedure cache, with no rhyme or reason.Labels: SQL Server
Read the article.
Permalink
If you have lots (and I mean LOTS -- we have 175 GB of school-related data, and our biggest per-table savings would have been 1.5 MB) of nulls in decimal/numeric columns with large precision, you may be able to save noticeable space with vardecimal, which requires only 2-4 bytes per column when the column is 0 or null.Labels: SQL Server
Read the article.
Permalink
KILL [spid] WITH STATUSONLY
shows the percent completion of the rollback.Labels: SQL Server
Permalink
Tuesday, October 9, 2007
A spinlock is a very lightweight lock used for accessing things which must be synchronized, like caches. They don't show up in the queryable wait indicators. When there is contention for them, you'll see lots of runnable (waiting to run) processes. This is more likely to happen on x64 systems because the caches can get bigger.Labels: SQL Server
Permalink
Thursday, March 8, 2007
I upgraded our development server from SP1 to SP2 of SQL Server 2005 this morning. It claims that the database engine upgrade failed (yes, that's as verbose as the error message got), yet all the server properties purport that it's now SP2.
At any rate, some of our databases were left in "RECOVERY_PENDING" status. Rebooting didn't help, so I tried the first result of my Googling -- set the database offline, then back online. Coming back online failed, but it returned a helpful error: operating system access denied. LO AND BEHOLD, the broken database files now had different NTFS permissions than the working databases. I made them all the same, and the databases happily came back online.Labels: SQL Server
Permalink
Thursday, July 20, 2006
When deploying database code, I'm running each SQL script on several servers, and having to click the "change connection" button dozens of times in SSMS is very frustrating. I submitted this as a bug to Microsoft, and Bill Ramos offered the following method for creating a keyboard shortcut:
- Open a Query Editor window connected to your favorite server.
- Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.
- Right click on Change Connection button again and then change the command name "C&hange Connection..." to "Chan&ge Connection...". This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.
- Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both "Image and Text". This exposes the Alt-G command to the Query Editor.
- You can now close the Customize dialog and use Alt-G as your hot key!
I love this shortcut. I use it dozens of times daily. It's still too much overhead for deploying, though, so I either use Query Analyzer (open a window, drag all the files into it, and they all open with a connection to that same server without prompting) or an application I wrote to accept a deploy list, get the files from our source control provider, and put them all into one script with appropriate USE [database] statements. Labels: SQL Server, SQL Server - SSMS
Permalink
Thursday, April 21, 2005
When you transfer SQL Server logins to a new server with DTS or create them on the new server manually, the SIDs don't match up with the server that you are importing your database backups from. To avoid this, Microsoft has created handy scripts (for transferring from 2000 to 2005, for transferring between 2005 instances). If you've already botched it up, you can find the orphaned logins like so:
EXEC sp_change_users_login 'Report'
And you can fix them one at a time like so:
EXEC sp_change_users_login 'Update_One', 'username', 'login'
which links users in databases with the username "username" to SQL Server logins with the name "login".Labels: SQL Server
Permalink
Monday, March 21, 2005
Don't use SQL Server without service packs! We upgraded one node of our sql cluster to a faster computer, and when the network admin went to install SQL Server SP3 on it, it claimed that it didn't need to do anything since it was a part of a cluster. When people started using the site, the CPU on the database shot up to 100% and we started mysterious getting SQL Server internal errors.
We eventually determined that even though Enterprise Manager was claiming that SP3 was installed, it wasn't. This site and this KB article describe how to more accurately figure out service-package.
Today's lessons are 1) don't believe Microsoft and 2) don't use any Microsoft product until they've patched it at least once!Labels: SQL Server
Permalink
|
|
