SSMU Web Seminar: Indexing for Performance

SSMU Web Seminar: Performance Tuning Series
Part 1: Methodology

Kimberly L. Tripp

Q & A

Q: How can I found out how many times a query is executed?

I answered this one during the seminar but I wanted to make sure that you felt clear about the options you need to put together using Profiler. First, you want to make sure that you don’t “lose” queries from the Profiler Workload… By setting the “Server processes trace data” you will guarantee that no queries are lost. From the BOL:

If selected, no events will be skipped under stress conditions; however, performance of the server may be affected, depending on the number of events being traced. If this check box is cleared, then the processing is performed by the client application, and there is a possibility that some events will not be traced under stress conditions.

You can certainly start by getting a trace without setting this option and see what queries are the most frequent BUT with a system under load you may not be getting a truly accurate picture.

Once you have the trace saved as a workload file you can import it into a table. If you choose NOT to set the “server processes trace data” then you can save the workload rows directly into a table.

Once the data is within a table – you can aggregate the “TextData” based on a substring of TextData. In fact, I’ve chosen two slides from Brian Moran’s “Quick Strike” methodology lecture titled: Solve Performance Problems Using A Repeatable, Structured Methodology. He titles this method of analyzing data creating “query classes.” Query Classes are where “enough” of the text is similar to consider it a group. If you’re interested in the entire session he will be delivering it at the SQL Magazine Connections conference in Palm Springs this October. For those of you with access to these slides – Brian’s slides have been placed directly into the slide deck immediately after slide 15: Finding Frequently Executed Queries. For more information about the upcoming SQL Magazine Connections conference, check out: www.sqlconnections.com.

Generally, the recommendation is to aggregate over 30-50 characters. For example,

SELECT LEFT(TextData, 30), COUNT(*)

FROM WorkloadTable
GROUP BY LEFT(TextData, 30)

HAVING COUNT > x

Q: I've done profiler traces before and the textdata has been incomplete (saved to file and table). Is there a way to see the entire TSQL statement which was executed?

I’m still looking into a few things on this one…

Q: Is there a more efficient way to do do user defined SORT BYs other than assembling the query on the application server?

I’m not sure I completely understand this but I suspect it’s really asking for further clarification for a point I was trying to make during the session…

Often applications bring large amounts of data down to the client and then format, sort and/or eliminate some rows there (after the server has processed the larger set and after the network has delivered significantly more data than necessary). My primary goal in optimization from a client-server perspective is ask the server for exactly what you want and make sure the server is optimized to handle it (i.e. indexes, optimized procedural code, etc.).

Now, having said that – there are exceptions (aren’t there always J)… IF an application wants a subset of data displayed in numerous sort orders over a period of time AND they’re OK with having a “stale” set of data… then it’s more than ok to deliver a reasonable amount of data to the client and manipulate it there (possibly with periodic refreshes).

There are lots of books regarding application best practices as well as tips and tricks on many application developer’s sites. One title that might interest you (and there are a few articles on SQL Mag as well) is “ADO and ADO.Net Examples and Best Practices” by William R. Vaughn. Bill will also be at SQL Magazine Connections in the fall.

Q: We get NULL for NTUserName in Profiler. Is that because we are using TCP/IP and not named pipes?

I’m still looking into a few things on this one…

Q: When you are going through profiler there are delays and you tend to go to quick through this demo area with the screens not appearing in a timely manner?

I apologize for this. I will make sure that we notice this type of comment faster next time. Hopefully the screen shots display better in the replay. More than anything I recommend two things to help you with Profiler: Watching the PSS WebCast (link at the end of this Q&A) AND Playing with Profiler. You’ll be surprised at how easy it becomes if you stay focused and limit the number of events and data columns as well as use lots of filter. Filter for a specific database, filter out system information (via the checkbox), add additional filters for other text that pops up if you’re not interested in seeing it! Once you focused Profiler to just what you need you won’t get overwhelmed by it (otherwise it’s just too much information).

Q: When a query hits a page, for say a limited number of columns, does SQL Server load the entire page into memory, or just the selected columns?

SQL Server’s smallest unit of I/O is a page. So the answer to this is YES! You should always consider the “physical” make up of a data row and try to keep you “internal” fragmentation on a page to a minimum. Internal fragmentation is when empty space is unintentionally left on pages within the table. The key word here is “un”-intentionally… Often administrators will leave a small amount of free space to minimize table fragmentation but too much and/or too many splits OR bad design – could make this less than optimal. There are lots of concepts here but some great resources out there to get more info. Check out:

Index Defragmentation Best Practices (listed at the end)

DBCC SHOWCONTIG (in the BOL)

Inside SQL Server 2000, Kalen Delaney

ISBN: 0-7356-0998-5

Q: How can I get a copy of your slides?

For those of you who attended the online seminar a copy of these slides are provided for your PERSONAL USE. As just an fyi – I rarely give out actual slide decks… It’s important to me that you keep these for your own personal use. Thanks a lot! I really appreciate your discretion.

Q: Is it useful to remain disciplined, say using TinyInt & SmallInt when appropriate, in hopes that the cumulative effect will be to fit more rows on datapages...or do you visit this only when necessary?

This is great! I actually struggled with whether or not I’d add a few slides about table design and/or row size optimization, etc. and I chose not to… But at least I can answer this here. YES!!! I always try to find the most appropriate data type for the job. I would make sure that you always use tinyint over smallint or int IF you truly only need less than 256 values. More than anything this is dictated by the data however here are a few rules:

For character data of less than 5 characters – even if the actual length of the data varies… use CHAR. There’s overhead for variable width data and it’s often not worth it considering the column width. Of course there could be exceptions (say where the column is usually 1 character (90% of the time) and 1-5 chars the rest of the time. You might save a tad by using varchar but nothing except some application testing, etc. will help you to determine whether or not the performance overhead outweighs the space savings. Also the space savings may be ZERO. Depending on how many rows fit on a page there’s almost always a small amount of free space – if you have a tad less free space on the page it really doesn’t change the overall space needed for the table. Again, lots of concepts here but BOL, Inside SQL Server and the Database Structures whitepaper on MSDN should all help with this!

For character data between 5 and 20 bytes – that’s much harder! It depends a lot on how much of it is used on average (per row) and whether or not the data is extremely volatile… In this grey area, use your best guesstimate.

For character data greater than 20 bytes – I almost always go varchar if the data varies. This is where the space savings starts to significantly outweigh the variable character data overhead.

For all of the other data types – just find the best one for the job. If you don’t really need the full range of date values supported by datetime use smalldatetime…

Resources:

Webcast: SQL Server 2000 Profiler: What’s new and how to effectively use Profiler!

http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc111400/wcblurb111400.asp

Here’s the general link for all webcasts from Microsoft:

http://support.microsoft.com/default.aspx?PR=pwebcst&FR=0&SD=MSDN&LN=EN-US&CT=SD&SE=NONA

Whitepaper: Index Tuning Wizard for Microsoft SQL Server 2000

http://msdn.microsoft.com/library/en-us/dnsql2k/html/itwforsql.asp?frame=true

Whitepaper: Improving Performance with SQL Server 2000 Indexed Views

http://msdn.microsoft.com/library/en-us/dnsql2k/html/indexedviews1.asp?frame=true

Whitepaper: Microsoft SQL Server 2000 Index Defragmentation Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp?frame=true

Here’s the general link for all technical resources/whitepapers on msdn: