Search

You're already capturing SQL trace information through the use of server-side traces, which send results directly to files on a locally attached disk on the server. Server-side traces not only […]

Every DBA knows that there are certain aspects of SQL Server and its associated hardware that you simply must monitor if you want to measure overall system performance. However, not […]

In my travels throughout the world as a SQL Server performance consultant, I find that many people have trouble determining where to start when they're looking for SQL Server performance [...]

The tempdb database is essential to normal SQL Server operation and is used extensively both by internal system-level SQL Server processes and by user-generated requests. In fact, tempdb is often [...]

As a performance consultant, I see SQL Server installations that range in size from the tiny to the largest in the world. But one of the most elusive aspects of [...]

One thing I hate to see while troubleshooting performance problems on clients' systems is a failure to reuse query plans. Before SQL Server can execute a batch file or stored [...]

A question I hear regularly from the SQL Server user community is, 'How do I know when it's time to defrag my indexes?' Most semi-experienced SQL Server DBAs use the […]

In two previous articles--'Getting to Know Wait Stats' (InstantDoc ID 96746) and 'Dissecting SQL Server's Top Waits' (InstantDoc ID 98112)--I discussed SQL Server 2005 waits and showed you some of […]

You already know how to parse trace files by using home-grown methods that involve SQL Server's common language runtime (CLR) functionality. That methodology remains flexible and powerful, but Microsoft has […]

Since the publication of my performance-comparison article, 'SQL Server Profiler or Server-Side Trace?' August 2008, InstantDoc ID 99365, I've received many requests for more information about how to use server-side […]

I'm sure you're familiar with the built-in SQL Server Profiler tool and the wonderful information it can return and display for you. Most DBAs use the tool on a weekly--if […]

A common misconception among SQL Server DBAs is that they need to rebuild their indexes often--even as often as every night. The theory is that, to be effective, indexes can't […]

I recently spoke with someone who was thinking about creating multiple SQL Server instances on his server and placing one database in each instance. You might wonder why he would […]

I often see too many indexes on a given table that turn out to be redundant or less useful than they might seem to be at first. If you have […]

Okay. You've read 'Getting to Know Wait Stats' (October 2007, InstantDoc ID 96746). You know about wait stats--what they are, how to collect them, and how to report on them. […]

How many times has someone asked you, 'How do I go about cleaning up all these unused indexes?' If you're like most DBAs, you've often fielded this question or even […]

People often ask me what the Max Degree of Parallelism (MAXDOP) setting should be in their SQL Server environment. Parallelism seems to be one SQL Server aspect that people don't […]

If your boss asked you for a list of the worst performing statements in your system, what would you do? Do you keep such a list handy? If you don't, […]

The article offers several tips on SQL Server performance tuning.

The article offers the author's insights on the proper use of the dynamic SQL in computer programming. He states that the sp_executesql method is the preferred way to execute dynamic SQL due to possible reuse query plans. He emphasizes the importance of using the sp_executesql in place of the execution of ad-hoc statements.

The article discusses database operations that require minimal transaction writing in the transaction log under specific conditions. It mentions that identification or index pages under a minimally logged operation are enough information to recover the transaction without the supporting point-in-time recovery. It notes that less data written to the log would significantly reduce the time to complete the operation, as well as the load on the server.

The article discusses the implications of reusing query plan to run a newly executed query for SQL Servers. It provides details about the procedure cache process that needed to be understood. It discusses two SQL Server options, which can help lessen the problems associated with lack of plan reuse, such as auto-parameterization and Optimize for ad hoc workloads.

The article discusses the four techniques in addressing SQL Server database performance problems including wait statistics, file statistics, top 10 efficient queries and plan reuse. It presents as to how go to about in each technique and suggests that one takes a little time in getting proficient with the said techniques so as to solve the said problems. Also mentioned is the input/output system and central processing unit (CPU) in computers.

The article provides techniques to improve system performance of central processing unit (CPU). It offers detailed examples of good utilization of file groups in a database. It is recommended to create only .25 to 1 data file per file group for each CPU on the host server to achieve good performance.

The article offers information on how to use of a dynamic management view to monitor the wait stats of the structured query language (SQL) server. The syntax Sys.dmiowaitstats can be used in materializing the in-memory values of the wait stats as a typical result set that represents the total waits for all processes that have finished since the counters were last reset. Moreover, each query will be provided with a snapshot which includes the total waits on the instance.