[MSSQL] General SQL Server Performance Tuning Tips (펌)

General SQL Server Performance Tuning Tips

BY BRAD MCGEHEE

When your transaction log grows large and you want a quick way to shrink it, try this option. Change the database recovery mode of the database you want to shrink from “full” to “simple,” then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the “active” portion of the log, which is very small.  Contributed by Tom Kitta.  

*****

If you need to delete all the rows in a table, don’t use DELETE to delete them, as the DELETE statement is a logged operation and can take a significant amount of time, especially if the table is large. To perform the same task much faster, use the TRUNCATE TABLE instead, which is not a logged operation. Besides deleting all of the records in a table, this command will also reset the seed of any IDENTITY column back to its original value.

After you have run the TRUNCATE TABLE statement, it is important then to manually update the statistics on this table using UPDATE STATISTICS. This is because running TRUNCATE TABLE will not reset the statistics for the table, which means that as you add data to the table, the statistics for that table will be incorrect for a time period. Of course, if you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimizer is using out-of-date statistics.  

*****

If you use TRUNCATE TABLE instead of DELETE to remove all of the rows of a table, TRUNCATE TABLE will not work when there are Foreign Key references present for that table. A workaround is to DROP the constraints before firing the TRUNCATE. Here’s a generic script that will drop all existing Foreign Key constraints on a specific table:

CREATE TABLE dropping_constraints 
( 
cmd VARCHAR(8000) 
)

INSERT INTO dropping_constraints 
SELECT 
‘ALTER TABLE [' + 
t2.Table_Name + 
'] DROP CONSTRAINT ‘ + 
t1.Constraint_Name 
FROM 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1 
INNER JOIN 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2 
ON 
t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME 
WHERE t2.TABLE_NAME=’your_tablename_goes_here’ 
DECLARE @stmt VARCHAR(8000) 
DECLARE @rowcnt INT 
SELECT TOP 1 @stmt=cmd FROM dropping_constraints 
SET @rowcnt=@@ROWCOUNT 
WHILE @rowcnt<>0 
BEGIN 
EXEC (@stmt) 
SET @stmt = ‘DELETE FROM dropping_constraints WHERE cmd =’+ QUOTENAME(@stmt,””) 
EXEC (@stmt) 
SELECT TOP 1 @stmt=cmd FROM dropping_constraints 
SET @rowcnt=@@ROWCOUNT 
END 
DROP TABLE dropping_constraints

The above code can also be extended to drop all FK constraints in the current database. To achieve this, just comment the WHERE clause.  

*****

Don’t run a screensaver on your production SQL Servers, it can unnecessarily use CPU cycles that should be going to your application. The only exception to this is the “blank screen” screensaver, which is OK to use.  

*****

Don’t run SQL Server on the same physical server that you are running Terminal Services, or Citrix software. Both Terminal Services and Citrix are huge resource hogs, and will significantly affect the performance of SQL Server. Running the administrative version of Terminal Services on a SQL Server physical server, on the other hand, is OK, and a good idea from a convenience point of view. As is mentioned in other parts of this website, ideally, SQL Server should run on a dedicated physical server. But if you have to share a SQL Server with another application, make sure it is not Terminal Services or Citrix.  

*****

Use sp_who or sp_who2 (sp_who2 is not documented in the SQL Server Books Online, but offers more details than sp_who) to provide locking and performance-related information about current connections to SQL Server. Sometimes, when SQL Server is very busy, you can’t use Enterprise Manager or Management Studio to view current connection activity via the GUI, but you can always use these two commands from Query Analyzer or Management Studio, even when SQL Server is very busy.  

*****

SQL Server uses its own internal thread scheduler (called the UMS) when running in either native thread mode or in fiber mode. By examining the UMS’s Scheduler Queue Length, you can help determine whether or not that the CPU or CPUs on the server are presenting a bottleneck.

This is similar to checking the System Object: Processor Queue Length in Performance Monitor. If you are not familiar with this counter, what this counter tells you is how many threads are waiting to be executed on the server. Generally, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck.

The advantage of using the UMS’s Schedule Queue Length over the System Object: Processor Queue Length is that it focuses strictly on SQL Server threads, not all of the threads running on a server.

To view what is going on inside the UMS, you can run the following undocumented command:

DBCC SQLPERF(UMSSTATS)

For every CPU in your server, you will get Scheduler. Each Scheduler will be identified with a number, starting with 0. So if you have four CPUs in your server, there will be four Schedulers listed after running the above command, Schedulers ID 0 through 3.

The “num users” tells you the number of SQL threads there are for a specific scheduler.

The “num runnable,” or better known as the “Scheduler Queue Length,” is the key indicator to watch. Generally, this number will be 0, which indicates that there are no SQL Server threads waiting to run. If this number is 2 or more, this indicates a possible CPU bottleneck on the server. Keep in mind that the values presented by this command are point data, which means that the values are only accurate for the split second when they were captured, and will be always changing. But if you run this command when the server is very busy, the results should be indicative of what is going on at that time. You may want to run this command multiple time to see what is going on over time.

The “num workers” refers to the actual number of worker threads there are in the thread pool.

The “idle workers” refers to the number of idle worker threads.

The “cntxt switches” refers to the number of context switches between runnable threads.

The “cntxt switches(idle)” refers to the number of context switches to “idle” threads.

As you can see, this command is for advanced users, and is just one of many tools that can be used to see internally how SQL Server is performing.  

Continues…

SQL Server  offers a system table-valued function that provides statistical information on the I/O activity of specific database files. It is called fn_virtualfilestats. What is interesting about this function is that it is specific to a database file. For example, if you want to see the I/O activity of a particular user database, you can. Keep in mind that all SQL Server databases have at least two physical files (for the database and log), and can have many more, depending on how the database was created. When using this function, you have to not only specify the database, but the file within the database. This is very specific I/O statistics indeed. To run this function, use the syntax found below:

SELECT * FROM :: fn_virtualfilestats(dbid, fileid)

Where:

dbid: The database ID of the database you want to examine.

fileid: The file ID of the physical files that make up your database. At a minimum, each database has at least two files: the database file (MDF file) and a log file (LDF). A database can have many files, and the file number refers to the number of the physical file that you want to examine.

To identify the dbid and fileid you want to use in the above statement, run the following SELECT statement. It will provide you with a list of all of the database names, database ids, and file ids (for each database) on your server.

SELECT sysdatabases.name AS Database_Name, 
     sysdatabases.dbid AS Database_ID, 
     sysaltfiles.fileid AS File_ID 
FROM sysaltfiles INNER JOIN sysdatabases 
     ON sysaltfiles.dbid = sysdatabases.dbid 
ORDER BY sysdatabases.name, sysaltfiles.fileid

Once you run this function, a wide variety of information is available, including:

NumberReads: The number of physical reads made against this file since the last time SQL Server was restarted.

NumberWrites: The number of physical writes made against this file since the last time SQL Server was restarted.

BytesRead: The number of bytes read from this file since the last time SQL Server was restarted.

BytesWritten: The number of writes to this file since the last time SQL Server was restarted.

IoStallMS: The total amount of time that users have waited for I/Os to complete for this file (in milliseconds).

The first four statistics can give you a feel for how busy a particular file is. This can come in handy when comparing multiple filegroups in a database and to see how balanced the I/O is to each file. To make the most of filegroups, I/O should be spread out among the various files for best overall performance. The last statistic, IoStallMS, is best used to find out if you have a bottleneck in your transaction log, as demonstrated below:

SELECT IoStallMS / (NumberReads+NumberWrites) as IsStall 
FROM :: fn_virtualfilestats(dbid, fileid)

Where:

dbid: The database ID of the database you want to examine.

fileid: The file ID of the transaction log of the database being examined.

Essentially, if IsStall is > 20ms, then this indicates that the I/O to the transaction log is becoming a bottleneck, which in turn can lead to major concurrently problems in the database.

To help optimize the transaction log, assuming it has become a bottleneck, consider doing the following:

• Place the transaction log on a faster disk array. 
• Place the transaction log on a dedicated disk array (no other files other than the transaction log). This allows sequential writes to occur as fast as possible, helping to boost I/O performance. 
• Turn on write-back caching on your disk controller, but only if it is backed up with a battery and has been tested to work with SQL Server. Normally, write-back caching is turned off because it can cause database corruption should the server crash. 
 

*****

Sometimes, a user thread has to wait until the resources it needs are available. Hopefully, this won’t happen often, but it is a fact of life. But sometimes, long waits can indicate potential performance problems that can be corrected, if you know where to look. Long waits can be caused by blocking locks, slow I/O, and other factors.

Fortunately, you can access the amount of time a user thread has to wait, which can tell you which user thread, if any, is taking more time that it should. For example, the query below can be run to identify any user threads that have to wait more than one second:

SELECT spid, waittime, lastwaittype 
FROM master..sysprocesses 
WHERE waittime > 1000

When you run the above query, all of the processes that have been waiting for greater than 1 second (1000 milliseconds) — both system and user — will be displayed. You will want to ignore system processes, focusing your efforts on spids that represent specific user threads. Spid will give you the user ID (you will have to match the spid to the appropriate user), waittime is the number of milliseconds that this user thread has been waiting, and lastwaittype will give you a clue as to what is waiting to occur.

In the above query, I have used 1000 milliseconds as a cutoff point, but you can use any amount of time you want. Generally speaking, any user thread that has been waiting for five seconds or more should definitely be evaluated for potential performance issues. Of course, you can choose any threshold you want.  

Continues…

By default, you cannot use a UNC (Universal Naming Convention) name to specify a location of where to store a SQL Server database or log file. Instead, you must specify a drive letter that refers to a local physical drive or array. But what if you want to store your database or log file on another Windows Server or a Network Appliance, Inc. storage system? You can, but you will have to set Trace Flag 1807 on your SQL Server to allow the use of UNC names.

*****

For a quick and dirty way to check to see if your SQL Server has maxed out its memory (and is causing your server to page), try this. Bring up the Task Manager and go to the “Performance” tab.

Here, check out two numbers: the “Total” under “Commit Charge (k)” and the “Total” under “Physical Memory (k)”. If the “Total” under “Commit Charge (k)” is greater than the “Total” under “Physical Memory (k)”, then your server does not have enough physical memory to run efficiently as it is currently configured and is most likely causing your server to page unnecessarily. Excess paging will slow down your server’s performance.

Another number to make note of is the “Available Physical Memory (K). This number should be 4MB or higher. If it is not, then your SQL Server is most likely suffering from a lack of physical RAM, hurting performance, and more RAM needs to be added.

If you notice this problem, you will probably want to use System Monitor to further investigate the cause of this problem. You will also want to check to see how much physical memory has been allocated to SQL Server. Most likely, this setting has been set incorrectly, and SQL Server has been set to use too much physical memory. Ideally, SQL Server should be set to allocate physical RAM dynamically.  

*****

When performance tuning a SQL Server, it is often handy to know if the disk I/O of your servers (and the databases on it) are mostly reads or mostly writes. This information can be used to calculate the ratio of writes to reads of your server, and this ratio can affect how you might want to tune your SQL Server. For example, if you find that your server is heavy on the writes, then you will want to avoid RAID 5 if you can, and use RAID 10 instead. This is because RAID 5 is much less efficient that RAID 10 at writes. But if your server has a much greater number of reads than writes, then perhaps a RAID 5 system is more than adequate.

One of the quickest ways to find out the ratio of reads to writes on your SQL Servers is to run Task Manager and look at the sqlservr.exe process (this is the mssqlserver or sqlserv service) and view the total number of I/O Read Bytes and I/O Write Bytes. If you don’t see this in Task Manager, go to View|Select Column, and add these two columns to Task Manager.

The results you see tell you how many bytes of data have been written and read from the SQL Server service since it was last restarted. Because of this, you don’t want to read this figure immediately after starting the SQL Server service, but after several days of typical use.

In one particular case I looked at, the SQL Server had 415,006,801,908 I/O bytes read and 204,669,746,458 bytes written. This server had about one write for every two reads. In this case, RAID 5 is probably a good compromise in performance, assuming that RAID 10 is not available from a budget perspective. But if the reverse were true, and there were two writes for every one read, then RAID 10 would be needed for best overall performance of SQL Server.  

*****

Internet Information Server (IIS) has the ability to send its log files directly to SQL Server for storage. Busy IIS servers can actually get bogged down trying to write log information directly to SQL Server. Because of this, it is generally not recommended to write web logging information to SQL Server. Instead, logs should be written to text files, and later imported into SQL Server using BCP, DTS, or SSIS.  

*****

SQL Server has a database compatibility mode that allows applications written for previous versions of SQL Server to run under the current version of SQL Server. In you want maximum performance for your database, you don’t want to run your database in compatibility mode (not all new performance-related features are supported).

Instead, your databases should be running in the current native SQL Server mode. Of course, this may require you to modify your application to make it compliant to the version of SQL Server you are running, but in most cases, the additional work required to update your application will be more than paid for with improved performance.  

*****

When experimenting with the tuning of your SQL Server, you may want to run the DBCC DROPCLEANBUFFERS command to remove all the test data from SQL Server’s data cache (buffer) between tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.

If you want to clear out all of the stored procedure cache, use this command, DBCC FREEPROCCACHE. If you only want to clear out the stored procedure cache for a single database (not the entire server) use DBCC FLUSHPROCINDB. All of these commands are for testing purposes and should not be run on a production SQL Server.  

*****

Orphan SQL Server sessions can negatively affect SQL Server’s performance. An orphan SQL Server session can occur when a client improperly disconnects from SQL Server, such as when the client loses power. When this happens, the client cannot tell SQL Server to properly close the connection, so the SQL Server connection remains open, even though it is not being used.

This can affect SQL Server’s performance two ways. First, orphaned connections use up SQL Server connections, which takes up server resources. Secondly, it is possible that the orphan connections may be holding locks that block other users; or temp tables or cursors may be held open that also take up unnecessary server resources.

The OS periodically checks for inactive SQL Server sessions, and if it finds any, it will notify SQL Server so that the connection can be removed. Unfortunately, this only occurs every 1-2 hours, depending on the protocol used. If orphaned SQL Server sessions become a problem, Windows Server’s registry can be modified so that it checks more often for orphaned connections.

Identifying an orphaned connection from SQL Server is very difficult, but if you can identify it, it can be removed by KILLing the connection using SSMS or by running the KILL statement.  

Continues…

For best performance, don’t mix production databases and development (test or staging) databases on the same physical server. This not only serves to better separate the two functions (production and development), but prevents developers from using up server resources that could be better used by production users.  

*****

When we think of performance, we usually think about speeding up our application’s performance. But another way to look at performance is to look at our performance as DBAs or Transact-SQL developers.

For example, one of the easiest ways to speed up our Transact-SQL coding, in addition to making it easier to maintain and troubleshoot our code once it is written, is to format it in an easy to read format.

While there are many different code formatting guidelines available, here are some basic ones you should consider following, if you aren’t doing so already:

Begin each line of your Transact-SQL code with a SQL verb, and capitalize all Transact-SQL statements and clauses, such as:

SELECT customer_number, customer_name 
FROM customer 
WHERE customer_number > 1000 
ORDER BY customer_number

If a line of Transact-SQL code is too long to fit onto one line, indent the following line(s), such as:

SELECT customer_number, customer_name, customer_address, 
     customer_state, customer_zip, customer_phonenumber

Separate logical groupings of Transact-SQL code by using appropriate comments and documentation explaining what each grouping goes.

These are just a few of the many possible guidelines you can follow when writing your Transact-SQL code to make it more readable by you and others. You just need to decide on some standard, and then always follow it in your coding. If you do this, you will definitely boost your coding performance.

*****

Be wary of allowing users to directly access your databases (especially OLTP databases) with third-party database access tools, such as Microsoft Excel or Access. Many of these tools can wreck havoc with your database’s performance. Here are some reasons why:

• Often these users aren’t experienced using these tools, and create overly complex queries that eat up server resources. At the other extreme, their queries may not be complex enough (such as lacking effective WHERE clauses) and return thousands, if not millions, or unnecessary rows of data. 
• This reporting activity can often lock rows, pages or tables, creating user contention for data and reducing the database’s performance. 
• These tools are often file-based. This means that even if an effective query is written, the entire table (or multiple tables in the case of joins) has to be returned to the client software where the query is actually performed, not at the server. Not only can this lead to excess server activity, it can also play havoc with your network.

If you have no choice but to allow users access to your data, try to avoid them hitting your production OLTP databases. Instead, point them to a “reporting” server that has been replicated, or is in the form of a datamart or data warehouse.  

*****

SQL Server   offers support of SSL encryption between clients and the server. While selecting this option prevents the data from being viewed, it also adds additional overhead and reduces performance. Only use SSL encryption if absolutely required. If you need to use SSL encryption, consider purchasing a SSL encryption processor for the server to speed performance.  

*****

SQL Server   supports named instances of SQL Server. You can run up to 16 concurrent instances of SQL Server   on the same server.

As you might imagine, each running instance of SQL Server takes up server resources. Although some resources are shared by multiple running instances, such as MSDTC and the Microsoft Search services, most are not. Because of this, each additional instance of SQL Server running on the same server has to fight for available resources, hurting performance.

For best performance, run only a single instance (usually the default) on a single physical server. The main reasons for using named instances is for upgrading older versions of SQL Server to newer versions of SQL Server, transition periods where you need to test your applications on multiple versions of SQL Server, and for use on development servers.  

*****

If you run the ALTER TABLE DROP COLUMN statement to drop a variable length or text column, did you know that SQL Server will not automatically reclaim this space after performing this action? To reclaim this space, which will help to reduce unnecessary I/O due to the wasted space, you can run the following command:

DBCC CLEANTABLE (database_name, table_name)

Before running this command, you will want to read about it in Books Online to learn about some of its options that may be important to you.

*****

Trace flags, which are used to enable and disable some special database functions temporarily, can sometimes chew up CPU utilization and other resources on your SQL Server unnecessarily. If you just use them for a short time to help diagnose a problem, for example, and then turn them off as soon as you are done using them, then the performance hit you experience is small and temporary.

What happens sometimes is that you, or another DBA, turns on a trace flag, but forgets to turn it off. This of course, can negatively affect your SQL Server’s performance. If you want to check to see if there are any trace flags turned on a SQL Server, run this command in Query Analyzer:

DBCC TRACESTATUS(-1)

If there are any trace flags on, you will see them listed on the screen after running this command. DBCC TRACESTATUS only finds traces created at the client (connection) level. If a trace has been turned on for an entire server, this will not show up.

If you find any, you can turn them off using this command:

DBCC TRACEOFF(number of trace)

*****

SQL Server offers a feature called the black box. When enabled, the black box creates a trace file of the last 128K worth of queries and exception errors. This can be a great tool for troubleshooting some SQL Server problems, such as crashes.

Unfortunately, this feature uses up SQL Server resources to maintain the trace file than can negatively affect its performance. Generally, you will only want to turn the black box on when troubleshooting, and turn it off during normal production. This way, your SQL Server will be minimally affected.  

Continues…

If you have ever performed a SELECT COUNT(*) on a very large table, you know how long it can take. For example, when I ran the following command on a large table I manage:

SELECT COUNT(*) from <table_name>

It took 1:09 to count 10,725,948 rows in the table. At the same time, SQL Server had to perform a lot of logical and physical I/O in order to perform the count, chewing up important SQL Server resources.

A much faster, and more efficient, way of counting rows in a table is to run the following query:

SELECT rows 
FROM sysindexes 
WHERE id = OBJECT_ID(‘<table_name>’) AND indid < 2

When I run the query against the same table, it takes less than a second to run, and it gave me the same results. Not a bad improvement and it took virtually no I/O. This is because the row count of your tables is stored in the sysindexes system table of your database. So instead of counting rows when you need to, just look up the row count in the sysindexes table.

There is one potential downside to using the sysindexes table. And that this system table is not updated in real time, so it might underestimate the number of rows you actually have. Assuming you have the database option turned on to “Auto Create Statistics” and “Auto Update Statistics,” the value you get should be very close to being correct, if not correct. If you can live with a very close estimate, then this is the best way to count rows in your tables.

*****

Do you use Enterprise Manager to access remote servers, possibly over a slow WAN link? If you do, have you ever had any problems getting SSMS to connect to the remote server?

If so, the problem may lay in the fact that if Enterprise Manager cannot make a connection within 4 seconds, then the connection attempt fails. To overcome slow network connections, you can change the default SSMS timeout value from 4 seconds to any amount of time you like.

To change the default timeout value, select Tools > Options from Enterprise Manager, and then select   the “Advanced” tab. Here, change the “Login time-out (seconds)” option to a higher number.  

*****

SQLDIAG.exe is a command line tools that collects information about SQL Server and writes it to a text file. It can be useful for documenting or troubleshooting your SQL Server. When you run this command when SQL Server is running, the following information is collected and stored in a text file called sqldiag.txt, which is stored in the mssqllog folder.

The text of all error logs 
SQL Server registry information 
SQL Server dll version information 
The output from these system stored procedures:

sp_configure 
sp_who 
sp_lock 
sp_helpdb 
xp_msver 
sp_helpextendedproc 
sysprocesses 
Input buffer SPIDs/deadlock information 
Microsoft diagnostics report for the server 
The last 100 queries and exceptions (if the query history trace was running) 
 

*****

Memory leaks can steal valuable memory from your SQL Server, reducing performance, and perhaps even forcing you to reboot your server. A memory leak occurs when a poorly-written or buggy program requests memory from the operating system, but does not release the memory when it is done with it. Because of this, the application can use up more and more memory in a server, greatly slowing it down, and even perhaps crashing the server.

Some memory leaks come from the operating system itself, device drivers, MDAC components, and even SQL Server. And of course, virtually any application can cause a memory leak, which is another good reason to dedicate a single server to SQL Server instead of sharing it among multiple applications.

Memory leaks are often hard to identify, especially if they leak memory slowly. Generally, memory leaks become apparent when you notice that your server is running out of available memory and paging becomes a big problem. A symptom of this is a SQL Server that runs quickly after being rebooted, but begins to run more and more slowly as time passes, and when the system is rebooted again, it speeds up again.

One way to help get rid of many memory leaks is to ensure that you always have the latest service packs or updates for your server’s software. But a memory leak you find may not have an immediate fix. If this is the case, you may be forced to reboot your server periodically in order to free up memory.

Identifying what is causing a memory leak is often difficult. One method involved using System Monitor to monitor all of the counters in the Memory object over time, seeing what is happening internally in your computer. Another method is to use Task Manager to view how much memory is used by each process. A process that seems to be using an unusual amount of memory may be the culprit.

*****

While SSMS can make some DBA and developer tasks a little bit easier, in other cases, it can cause performance problems. For example, if you decide to change the schema of a table from within SSMS, you may find out that doing so takes much longer than performing the same schema change by using the Transact-SQL ALTER command. This is because SSMS  may not use ALTER, but instead recreate an entire new table based on your new schema, and the move the data from the old table to the new table, which can take a long time for large tables. Generally, using ALTER is very efficient and schema changes occur quickly, even on large tables.

Because of this, consider using ALTER to modify the schemas of very large tables instead of SSMS.  

*****

If you want to see a detailed accounting of how memory is being used in your SQL Server, run this undocumented DBCC command:

DBCC MEMORYSTATUS

The results look similar to this:

Buffer Distribution          Value 
—————————— ———– 
Stolen                         2914 
Free                         3049 
Procedures                    43521 
Inram                         0 
Dirty                         8366 
Kept                         1209 
I/O                         0 
Latched                    0 
Other                         163981

Buffer Counts               Value 
—————————— ———– 
Commited                    223040 
Target                         223040 
Hashed                         173556 
InternalReservation          202 
ExternalReservation          0 
Min Free                    201

Procedure Cache               Value 
—————————— ———– 
TotalProcs                    28301 
TotalPages                    43521 
InUsePages                    19676

Dynamic Memory Manager     Value 
—————————— ———– 
Stolen                         46436 
OS                         507 
General                    7137 
QueryPlan                    39310 
Optimizer                    0 
Utilities                    13 
Connection                    483

Global Memory Objects          Value 
—————————— ———– 
Resource                    932 
Locks                         78 
XDES                         1 
SQLCache                    1195 
Replication                    2 
LockBytes                    2 
ServerGlobal               71

Query Memory Manager          Value 
—————————— ———– 
Grants                         0 
Waiting                    0 
Available                    150336 
Maximum                    150336

Optimization Queue          Value 
—————————— ———– 
Optimizing                    0 
Waiting                    0 
Available                    16 
Maximum                    16

The value refers to the number of 8K buffers. [7.0, 2000] Updated 5-7-2007

Continues…

If you would like to see what is stored in SQL Server’s stack, you can by running this undocumented SQL Server DBCC command:

DBCC STACKDUMP

When you run this command, a stack dump will be performed, writing a file named SQL00001.dmp in the SQL Server log folder. This is a text file you can view with Notepad.

*****

Many times it is useful to compare the performance of a query or stored procedure as you are testing it. This is especially true if the time differences are so small that it is hard for you to know for sure which query was faster or slower.

One of the easiest ways to measure the time it takes for a query to stored procedure to run is to find the starting and ending times, then find the difference between the two. This can be easily accomplished using a few extra lines of Transact-SQL code. Here’s how:

DECLARE @starttime datetime 
SELECT @starttime = GETDATE()

… Your code here …

SELECT DATEDIFF(ms, @starttime, GETDATE()) AS ‘Total Time (ms)’

The results will be how long, in milliseconds, it took your code to run. SQL Server’s time function is only accurate to +- 3 milliseconds, so keep this mind.

*****

In SSMS, the most common way to script currently existing objects is to right-click on the database in question and select “All Tasks,” then choose “Generate SQL Script,” and so on, until you script the object you want. This works fine, but takes a lot of keystrokes.

Another way, from within SSMS, to script existing objects is to follow these steps instead:

• Open the database and right-click on the object you want to script, then click “Copy.” Or if you like keyboard shortcuts, press CTRL-C. 
• Open a copy of Query Analyzer, Notepad, or any text editor. 
• Now on the “Edit” menu from the application, click “Paste” (or press CTRL-V), and the scripted code appears, including any appropriate constraints.

This is a quick and easy way to script database objects you may find handy.

*****

Have you ever wondered how people find out about “undocumented” stored procedures? Have you ever wondered about how SQL Server works behind the scenes? And have you ever wanted to see some examples of how top-notch Transact-SQL developers write SQL code?

All of the above is at your fingertips, although you won’t see many references to it in Microsoft’s documentation. To accomplish all of the above, all you have to do is go to your master SQL Server database in SSMS, open the Stored Procedures Folder, and take a look. All SQL Server stored procedures can be seen here, even the ones not documented by Microsoft. To view the Transact-SQL code in a stored procedure, right-click on the stored procedure you are interested in and click Properties.

If you want to use Query Analyzer instead, all you have to do is to use this command:

USE master 
sp_helptext [system_stored_procedure_name]

In many cases, the Transact-SQL code you see is documented (although generally not in great detail), and if you know Transact-SQL, you should be able to follow the code and understand how it works. In some cases, you may need to experiment a little to better understand what is going on.

For example, here’s the Transact-SQL code for the sp_updatestats system stored procedure:

CREATE PROCEDURE sp_updatestats 
AS

DECLARE @dbsid varbinary(85)

SELECT @dbsid = sid 
FROM master.dbo.sysdatabases 
WHERE name = db_name()

/*Check the user sysadmin*/ 
IF NOT is_srvrolemember(‘sysadmin’) = 1 AND suser_sid() <> @dbsid 
BEGIN 
RAISERROR(15288,-1,-1,’SA or DBO’,'sp_updatestats’) 
RETURN (1) 
END

DECLARE @exec_stmt nvarchar(540) 
DECLARE @tablename sysname 
DECLARE @uid smallint 
DECLARE @user_name sysname 
DECLARE @tablename_header varchar(267) 
DECLARE tnames_cursor CURSOR FOR SELECT name, uid FROM sysobjects WHERE type = ‘U’ 
OPEN tnames_cursor 
FETCH NEXT FROM tnames_cursor INTO @tablename, @uid 
WHILE (@@fetch_status <> -1) 
BEGIN 
IF (@@fetch_status <> -2) 
BEGIN 
SELECT @user_name = user_name(@uid) 
SELECT @tablename_header = ‘Updating ‘ + @user_name +’.'+ RTRIM(LOWER(@tablename)) 
PRINT @tablename_header 
SELECT @exec_stmt = ‘UPDATE STATISTICS ‘ + quotename( @user_name , ‘[‘)+’.’ + quotename( @tablename, ‘[‘) 
EXEC (@exec_stmt) 
END 
FETCH NEXT FROM tnames_cursor INTO @tablename, @uid 
END 
PRINT ‘ ‘ 
PRINT ‘ ‘ 
raiserror(15005,-1,-1) 
DEALLOCATE tnames_cursor 
RETURN(0) — sp_updatestats

GO

Besides learning how system stored procedures work, and getting an opportunity to see professionally written code, (if you really like to get your hands dirty) you can also modify the code to do something slightly different. By this, I don’t mean to modify the code you see (that wouldn’t be prudent) but you can copy the code to a new stored procedure, modify it, and then save it with a different name.  

Continues…

On very rare occasions, you may get an error similar to this from SQL Server:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 845: [Microsoft][ODBC SQL Server Driver][SQL Server]Time-out occurred while waiting for buffer latch type 3 for page (2:0), database ID 15.

This error message may also be accompanied by additional error messages, such as a file log or database backup that failed.

This error indicates that your disk I/O system is being overloaded and that SQL Server timed out when trying to write data to disk (via the OS). If this error is a rare event, then there is no need to worry.

But if this event becomes frequent, it could mean that your disk I/O subsystem has hit a bottleneck that it can’t overcome, or that your disk I/O subsystem itself is having problems. You need to find out which one of these is the most likely issue, and then deal with it accordingly.

One of the things that you might want to look for is what I/O activity was happening at the time of the error. For example, it is possible that a disk backup, a DBCC command, or other database activity was all happening at the same time, overwhelming the disk I/O. Perhaps the solution to the problem is as simple as rescheduling some scheduled tasks.  

*****

According to Microsoft, the way to shrink a log file is to use DBCC SHRINKFILE. This process is painfully slow and inefficient. Even after running the procedure numerous times, you may not get much free space.

Here’s another way to shrink your logs that is guaranteed to work every time. First, back up the database and then detach (sp_detach_db) it (you will have to bring the database down to do this). Next, delete the log file and then re-attach (sp_attach_db) the database, not providing the old log file location from within the sp_attach_db command. This will create a new log file with the old log file name in the old location with default size i.e. 512 KB.

To make sure there is no problem during the operation, the old log file can be renamed and kept until the database is reattached successfully. This provides a backup plan if for some reason SQL server fails to attach the database without the old log file.

This trick won’t work if the database has more than one log file, but if you need to, you can alter the database so that it only has a single log file, while will allow you to perform the above steps. After carrying out the detach and attach database activity, the database can be again altered to add more log files.   Contributed by Gaurav Bindlish.  

*****

In SQL Server’s Query Analyzer, a somewhat hidden, but useful feature exists that you may not be aware off, and that is the Transact-SQL Debugger. The reasons you may not have seen it before in Query Analyzer is that you don’t see this option in any standard menu, making it somewhat hard to find.

To use the Transact-SQL Debugger, you must first ensure that the Object Browser is displayed from within Query Analyzer. Next, using the Object Browser, open up a database that has one or more stored procedures, then right-click on the stored procedure’s name, and then select Debug. This is how you turn on debugging for a stored procedure.

Once you click on Debug, if the stored procedure you selected has one or more input parameters, you will be asked to enter them, and then you can begin the debugging process. The Transact-SQL Debugger has the typical features you find in most any debugging tool, including:

• Go: Puts the stored procedure into the debugging mode. 
• Toggle Breakpoint: Sets or removes a breakpoint at the current line in your code. You cannot set a breakpoint on lines containing non-executable code. 
• Remove All Breakpoints: Clears all the breakpoints in your code. 
• Step Into: Step Into executes the statement at the current execution point, and then executes one code statement at a time. If the statement is a call to another stored procedure, then the next statement displayed is the first statement in the stored procedure. 
• Step Over: Executes one code statement at a time. If the current statement contains a call to another stored procedure, Step Over executes the stored procedure as a unit, and then steps to the next statement in the current stored procedure. 
• Step Out: Executes the remaining lines of code in which the current execution point lies. The next statement displayed is the statement following the procedure call. All of the code is executed between the current and the final execution points. 
• Run to Cursor: Specifies a statement further down in your code where you want execution to end. 
• Restart: Restarts execution at the beginning of the stored procedure. 
• Stop Debugging: Stops debugging. 
• Watch: Displays the current watch expressions. 
• Callstack: Lists the procedures calls that have started, but have yet to be completed. 
• Auto Rollback: Automatically rolls back all work performed during execution of the stored procedure.

*****

Have you ever wanted to test the latest beta software, but didn’t want to put it on your “production” desktop because you were afraid it might crash it, and didn’t have a separate test box available to run it on? Or have you wanted more than one test environment, but didn’t have the necessary hardware?

In both of these cases, there is a new option that allows you to do both, while not requiring any more hardware. How? By using Microsoft Virtual PC 2007. This software, which is designed to run on most desktops and Microsoft operating systems, allows you to install virtual servers on your current desktop (assuming your hardware is big enough).

For example, if you are running Windows XP Workstation on your desktop, you can create one or more multiple virtual servers that run almost any other Microsoft operating system, along with your choice of application software, such as SQL Server running under Windows Server, or any combination you like.

Virtual servers don’t have to be active all the time, you can bring them up only when you want to, helping to preserve hardware resources. You can create as many virtual servers as you like, plus you can have as many running as you like, assuming your hardware will handle them.

If you decide to do this, there are two things you may want to keep in mind. First, when you create a virtual server, you must specify how the networking will work. There are several options. You may have to experiment with these options to select the one that works best for you. Also, you will want to install the optional Virtual Machine Additions into your virtual server, after it is built. This will greatly enhance the performance of the virtual software. Information on how to do this is available in the instructions.

Microsoft also offers a “server” version.  

*****

To quickly identify which, if any, of your procedure is executed when SQL Server starts, you can use the following query:

USE master 
GO 
SELECT 
name 
FROM 
sysobjects 
WHERE 
OBJECTPROPERTY(id, ‘ExecIsStartup’) = 1

However, you can also get the same result via the INFORMATION_SCHEMA views, which are generally preferable over directly querying the system tables. Here’s the query:

USE master 
GO 
SELECT 
ROUTINE_NAME 
FROM 
INFORMATION_SCHEMA.ROUTINES 
WHERE 
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), ‘ExecIsStartUp’) = 1

출처 : http://www.sql-server-performance.com/2007/gen-tips/7/