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