ÿþ<html> <body> <h1>Some SQL Server 2005 / 2008 performance considerations.</h1> <B>Version</B> : 2.9<br> <B>Date</B> : 17/08/2010<br> <B>By</B> : Albert van der Sel<br> <B>Type of doc</B> : It's just a few notes on SQL Server performance in simple words. It's no more than just "entry level".<br> <B>For who</B> : For anyone who likes a short orientation on the subject. But for experienced DBA's, it's too simple.<br> <hr/> <br> <font face="arial" size=2 color="black"> <br> <B>Contents:</B><br> <br> <B>0. Preface</B><br> <B>Chapter 1. How to "quickly" determine if you have Disk I/O problems.</B><br> - 1.1 Use of the "fn_virtualfilestat" function<br> - 1.2 Using "System Monitor"/"Performance Monitor" Disk counters with respect to Disk IO<br> - 1.3 Information from the "sys.dm_os_wait_stats" view, with respect to IO and Disk IO<br> <B>Chapter 2. Some remarks on Virtual Machines and a large, active, SQL Server environment.</B><br> <B>Chapter 3. Some remarks about a 64 bit OS and 64 bit SQL Server edition.</B><br> <B>Chapter 4. Indexes and Index tuning.</B><br> - 4.1 Short recap on indexes <br> - 4.2 Dynamic management views and functions<br> - 4.3 Move a hotspot index to another Filegroup<br> - 4.4 Quick check on the effectiveness of your indexes<br> - 4.5 How to Reorganize and Rebuild Indexes<br> - 4.6 Remarks on Statistics<br> <B>Chapter 5. How to determine if you have a CPU problem .</B><br> - 5.1 Using "System Monitor"/"Performance Monitor" <br> - 5.2 Using the Dynamic Management Views and Functions<br> <B>Chapter 6. Allocation Unit and Partition Alignment.</B><br> <B>Chapter 7. Placement of objects on Filegroups.</B><br> - 7.1 The "traditional" non-partitioning approach<br> - 7.2 Partitioning tables and indexes<br> <B>Chapter 8. Other remarks on several subjects (like tempdb, datatypes etc..)</B><br> <B>Chapter 9. Some real world cases, describing somewhat more complex performance problems</B><br> <B>Chapter 10. A few links to recommended technical articles</B><br> <br> <br> <h2>0. Preface</h2> This is a short note, introducing some basic considerations on SQL Server performance.<br> <br> If you are an experienced SQL Server DBA, this note is probably not for you (but you are certainly invited to read on!).<br> <br> Instead, if you <I>just know you way around in SQL Server</I>, and you <I>just</I> want to know some of the more important<br> stuff on performance, then you are at the right place.<br> But.., remember, this note is surely no more than a lightweight discussion.<br> <br> <B>And a very important remark should be made right at the start: <I>we are not going to discuss "Query Design and Tuning"</I></B><br> <br> There are many things which are very important to improve SQL server performance. Like for example, to place<br> Table data and Indexes on different Disk Volumes (different "spindles"), there are memory considerations,<br> there are "smart" choiches on the datatypes, considerations if you need partitioning etc.. etc..<br> One very paramount subject in performance considerations (at all Database Engines like Oracle, SQL Server etc..), <br> is "Query Design & Tuning". Now, this note will not address that subject.<br> <br> What is meant by Query Design and Tuning ? <br> <br> Ultimately, Users or applications, send SQL Statements to the database engine.<br> <br> For the sake of argument, let's consider some complex SQL statement. It could consist of, say,<br> <B>20 or so nested joins</B> of possibly a mix of small tables, large tables, and possibly <I>very</I> large tables.<br> <br> Now, there are many roads that lead to the city of Rome, and some of them are easy to travel,<br> while others are difficult and lengthy. The same is true for how someone designs a query and how it will be executed.<br> It could be a very smart one, using the best and fastest accesspaths, or it might be so terribly ill-designed, <br> that the database Server just whished someone pressed the shutdown button.<br> <br> So, query design and tuning, is extremely important ! Yet, we don't mentioned it in this note.<br> Ofcourse, <B>one element</B> in Query Tuning <B>will</B> be addressed, and that is determining the best use of indexes.<br> But, the subject on how <I>to best actually write Queries, is not.</I> And believe me, it is a very important subject.<br> If some application uses horribly bad designed SQL Statements, it's really very hard to fight that.<br> <br> Note: you might not even be in control on Query Design at all. Suppose you use a "third-party" application (which is very likely).<br> Then it just works the way those developers created it. But even then you can "capture" the SQL statements, for example using<br> a 'tracing tool' (the profiler), or by using some smart queries on the <B>Dynamic Management views and functions</B>.<br> Then, in principle, you can hold the results to those developers, along with recommendations.<br> But, yes, that might be a bit of an "optimistic" view on matters.<br> On the other hand, if you have "internal" developers, you could create code for them in "stored procedures" (works fast),<br> or make recommendations for their query design<br> <br> Could this note then <I>has something to say at all?</I><br> Definitely! There are so many considerations on SQL Server peformance.<br> <br> <I>Below, you will find about 8 considerations, in 8 chapters, which are generally viewed to be "relevant"....</I><br> <br> <h2>Chapter 1. How to "quickly" determine if you have Disk I/O problems.</h2> Many learned people will tell us, that a <I>good performance measurement</I>, should always consider cpu, the disksubsystems,<br> memory, the network subsystem, and all relevant specific SQL server counters.<br> That is true. But it does not mean that you can't focus for a while on just one particular subsystem only.<br> If we do that, the only thing we should beware of, is that we should not jump to conclusions right away. Ok, that's fair enough.<br> <br> Still, the following gives us very important clues on the status/statistics of Disk IO.<br> <ol> <li> A few "dynamic management views and functions" in SQL Server, can show you quickly whether Disk IO is good or bad, or something in between. <br> The <B>cause</B> of poor Disk IO, could originate from a Database design which is not OK (like not having seperated tables and indexes),<br> <I><U>or</I></U>, indeed the disksubsystem cannot keep up with the demands.<br></li> <li> Also, a number of counters of "System Monitor" (or Performance Monitor) in NT, will show you quickly the same thing.</li> </ol> <br> <h3>1.1 The "remarkable" <B>fn_virtualfilestats</B> function </h3> One remarkable function, is the <B>fn_virtualfilestats</B> function. You can use it from the "Query Window" (or Query Analyzer)<br> which you can start from "SQL Server Management Studio".<br> It returns I/O statistics for database files, including log files. To use it, it can be as easy as this statement:<br> <br> <B>SELECT * FROM fn_virtualfilestats(null, null)</B><br> <br> <br> The function takes two parameters. If you leave them as "null", you will see IO statistics on all files from all databases.<br> That ofcourse, could already be good enough. Below, you see an example of the output.<br> <br> <img src="sql1.jpg" align="left"/> <br> Before we discuss this very interresting output, here is some more info on the function itself. If you want to use parameters (for a shorter list), then you should know that the first parameter is the "database id" (dbid), and the second one is the "file id" (fileId).<br> This way, you can "focus in" to a particular database and/or particular file.<br> <br> It's easy to get a list of database names and database id's using:<br> <br> <B>select * from sys.databases</B><br> <br> Likewise, it's easy to get a list of filenames and file id's for a particular database, using:<br> <br> <B>use database_name_of_your_interest<br> select * from sys.database_files</B><br> <br> Now let's turn our attention to the output. As you can see, you find a number of very interesting columns in the resultset.<br> Here are a few important ones:<br> <br> <br> <B>BytesRead: Number of bytes read issued on the file.<br> BytesWritten: Number of bytes written made on the file.</B><br> <br> <I>Note that you can easily identify "hotspot files" from those values...(!)</I><br> <br> <B>IoStallReadMS: Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.<br> IoStallWriteMS: Total time, in milliseconds, that users waited for the write I/Os to complete on the file.</B><br> <br> The last two values, thus represent the "stalls" on reads and writes. Obviously, they should take on small values.<br> If you see high values, It does not automatically means that you have a "bad" Disksubsystem design.<br> Ofcourse, that could be true, but also other configurations can contribute to the effect.<br> For the latter, you might think of large tables and large non-clustered indexes which are stored in the <I>same</I> file,<br> and thus a lot of <I>contention</I> takes place, which can result in the "stalls" observed.<br> <br> So, the output of the "fn_virtualfilestats" function, cannot lead to exact conclusions right away.<br> But:<br> <ol> <li>You can identify files which are "hotspots", thus which have a large number of reads and/or writes.<br> This could mean that you must move a (few) "active" table(s) or index(es) to a new file on another disk.</li> <li>You identify files where "stalls" takes place. This could mean that the Disk IO design is not good enough,<br> but that fact is not proven yet. Just as above, here too it could could mean that you must move a (few) "active" table(s) or index(es)<br> to a new file on another disk.</li> </ol> But if you have neatly seperated active indexes and tables, and <I>there still are many stalls</I>,<br> then, it might be a clue that Disk IO is not optimally setup. But, we do not jump to conclusions yet!<br> <br> In our example, we have seen a Disk IO problem. But it could either be caused by a bad database design, or possibly by not optimally<br> configured disks.<br> <br> You see? We already have learned a lot. But we need more information, so let's see what the next section brings us.<br> <br> <h3>1.2 Using "System Monitor"/"Performance Monitor" Disk counters with respect to Disk IO</h3> System Monitor (or Performance Monitor as many people still call it), is a well-know NT performance measurement utility.<br> You can do "real-time" measurements (viewing real-time graphs), or you can log the findings to a file.<br> If you want to start it, just open a command window and enter the <B>perfmon</B>command.<br> <br> With NT system monitoring tools, you will encounter the following naming structure;<br> - "objects" are representations of (real) components like processor, physicalDisk etc..<br> - a "counter", of an object, is a measurable metric that is exposed by that object. An object usually has many counters.<br> - "instance", for example, you might have an object like a "PhysicalDisk", but.. you might have multiple disks on your system!<br> So, in this example, you might pick a particular disk (like E:), or choose all of them (mostly designated by "_Total")<br> <br> So you might have as an object, a "processor", which exposes several counters like "%User Time", "%Priviledge Time", or<br> "%Processor Time" (which is User + Priviledge).<br> <br> Using this graphical tool (performance monitor/system monitor), it's quite easy to select multiple objects, and per object,<br> select the counters that you are interrested in.<br> This chapter deals with the subject on how you can discover Disk I/O problems, so we will focus our attention to that subject.<br> <br> If you want to add counters, just navigate to the "graph" section and right-click. Then you will see a menu to add counters.<br> There objects (each with many counters) which describe your system and OS, like processor, memory etc..<br> And, if your Server has SQL Server installed, there are many objects from SQL Server as well.<br> <br> <B>Standard system Disk counters:</B><br> <br> First let's take a look at the standard system counters, related to the Disk subsystem.<br> Note: maybe you want to check with your sysadmin whether disk counters are "activated" (it's very likely that it is).<br> <br> As objects, you can choose "PhysicalDisk" and "LogicalDisk". Logical Disks are structures like "partitions", like an E: drive.<br> In this discussion, it does not matter which one you choose, <I>as long as it is the disk/partition you want log, that is,<br> it should contain databasefile(s), or the transactionlog file(s).</I><br> So, for example you might choose "G:", because this "disk" contains an active Index file.<br> Or, you might choose the "_Total" object of the LogicalDisk object, which means you measure all disks at the same time.<br> And if you have a dedicated SQL Server machine, then that's not a bad choiche.<br> <br> Now, pay special attention to the following counters (from the PhysicalDisk object):<br> <br> <B> % Disk Time</B><br> <B> Avg. Disk Queue Length</B><br> <br> <img src="sql4.jpg" align="center"/> <br> In the figure on the left, you see a very "exaggerated" example of a perfmon graph. Note the various counters diplayed. The "yellow" line is "%Disk Time", while the "green" line represents the "Avg. Disk Queue Length".<br> <br> The values shown here, are a bit high. Who cares, it's just an example.<br> <br> The "PhysicalDisk: % Disk Time" counter monitors the percentage of time that the disk is busy with read/write activity.<br> <br> If the "PhysicalDisk: % Disk Time" counter is constantly high, (more than 80,90 percent), we might see a problem.<br> <br> <br> Even of more interrest, is the "PhysicalDisk: Avg. Disk Queue Length" counter, to see how many system requests are <U>waiting</U><br> for disk access. If you see that taking values like 2, 5, 3, 4, 10, 1 etc.., there is likely no Disk IO problem.<br> <br> <br> If you want to see that expressed in a rule: the number of oustanding request should be no more than 2 x (the number of spindles).<br> So, suppose you see constantly high values like 20, 30 or higher, you might agree we probably have a serious Disk IO problem.<br> <br> Also note from the above figure, that I took the counters of the "PhysicalDisk" object, over all "disk instances" (shown as "_Total").<br> <br> As always, however compelling the "evidence" seems to be, never jump to conclusions right away. We still need more information.<br> <br> Now, what about the specific SQL Server counters? Sure, and we will see them in Chapter 6!<br> <br> <br> <h3>1.3 Information from the "sys.dm_os_wait_stats" view, with respect to IO and Disk IO</h3> Since version 2005, SQL Server has a very extended dictionary with "system views" and "system functions", for use for the DBA.<br> The views contains a wealth of statistics on sessions, locks, transactions, system metrics like latches, Disk statistics etc.. etc..<br> At last, we have reached the level of Oracle DBA's, which for many many years queried their hundreds of v$ and DBA_ views.<br> <br> There are so many interresting system views in SQL Server, that we want to view them all right now! But, this chapter is<br> dealing on Disk IO, so one Dynamic Management view stands out: "sys.dm_os_wait_stats".<br> This view is specifically for gathering "waits" on a large number of events.<br> (Note: even the function of section 1.1 uses it)<br> But we must remember that the values in "sys.dm_os_wait_stats", are "cumulative", that is, information is added all the time.<br> The counters can be reset using the SQL statement DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR).<br> <br> You could already use "select * from sys.dm_os_wait_stats", but that query gives use too much (unfocused) information.<br> <br> <img src="sql2.jpg" align="left"/> <br> In the figure on the left, you see a better query variant on sys.dm_os_wait_stat. We specifically leave out fields which we are not interrested in (at the moment) because we want to focus on events related to Disk IO.<br> That's why we use the clause "where waittype not in (..)".<br> <br> And, we use the clause "top 10 *" because we are interrested in the "top 10 IO metrics".<br> <br> Note: below you will find the query in clear text).<br> <br> Also note that you can write nummerous interresting query variants on sys.dm_os_wait_stats.<br> <br> Let's now focus on the output of this specific query. In this specific example, the query was run on a system which has quite a Disk IO problem. A very important column to pay attention to is "wait_time_ms", which shows us the time in ms, which processes had to wait for, for that specific "wait_type" (shown in the first column).<br> <br> What could set us on the track of possible Disk IO problems, is the "wait_time_ms" related to the<br> "Pageiolatch_%" wait_types.<br> "Pageiolatch_sh means that a session is waiting for some page to be brought into memory, from database files on disk.<br> That a small wait is involved is understandable. However, if the Pageiolatch_% waittimes are too high, it could be an indicator of a Disk IO problem.<br> More generally, this wait_type, represent all sorts of memory-to-disk transfers, so it could also be an indication that you are low<br> on cache buffer memory.<br> <br> IO problems can also be be a considerable factor, if ASYNC_IO_COMPLETION and IO_COMPLETION show high wait times.<br> <br> <br> The values are cumulative ofcourse, and you might consider the above DBCC command, to start over.<br> What are reasonable values of the Pageiolatch_% values?<br> The query that is shown in the figure, <I>actually tells you</I>. If it's listed high in the top 10, we have an indicator of a Disk IO problem.<br> <br> Again, don't jump to conclusions from this information alone. However, if we take all the information from<br> sections 1.1, 1.2 and 1.3 together, and they provide a consistent view, then it is reasonable to assume that Disk IO<br> is indeed a problem.<br> So, we did what was promised in the title of this chapter: <I>How to "quickly" determine if you have Disk I/O problems.</I><br> <br> However, <B>we did not make clear</B> whether the IO problem arose from a bad database design (seperation tables and indexes etc..),<br> or that indeed the disksubsystem is not good enough for the SQL Server environment.<br> <br> As another example to be carefull before "you blame something", consider this:<br> Maybe there are so many users active, which was not anticipated before, so that the system is not<br> scaled properly. There are just so many competing sessions, that everything "overloads".<br> Ofcourse we can investigate that too.<br> It's just a remark "to be carefull" with conclusions.<br> <br> Note: here is the query (for easy copy/paste purposes)<br> <br> <font face="courier" size=2 color="black"> select top 10 *<br> from sys.dm_os_wait_stats<br> where wait_type not in<br> ( <br> 'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',<br> 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',<br> 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',<br> 'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', <br> 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', <br> 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',<br> 'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',<br> 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', <br> 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'<br> ) <br> order by wait_time_ms desc <br> <font face="arial" size=2 color="black"> <br> <h2>Chapter 2. Some remarks on Virtual Machines and a large, active, SQL Server environment.</h2> In case of a very large, very "active" database, better not virtualize your SQL environment. Well, that's my opinion.<br> <br> <I>Please read the following with a healthy dosis of skeptism. It's just that on this subject, nobody can tell<br> you "the absolute truth".</I><br> <br> In today's Datacentres, many physical Servers are present, where <U>each</U> physical machine is running <U>many</U> "Virtual Machines (VM)".<br> Almost always, some "virtualization" product is used by which the Admin is able to create VM's on that Host.<br> Those VM's, each get their share of memory, virtual cpu's, network access, and disk access.<br> For example, "VMware ESX" is very popular. One physical Server, installed with ESX, can run a mix of all sorts of Linux and Windows VM's<br> <br> It does not have to be a problem. But fact is, that many physical Hosts gets "crowded" with competing VM's for resources.<br> This is the way many "businesses" work: new projects get alive, which all need their own test- development- and production machines.<br> In many cases, it's hard for a sysadmin to keep all the demands and resources in balance.<br> <br> Secondly, when the sysadmins and SAN administrators create the infrastructure, they may be unaware of the specific purposes<br> of the individual VM's that will be installed. Some VM's will work as File Servers, Mail Servers, Domain Controllers, Application Servers,<br> and.... SQL Server machines.<br> <br> Now, you might end up with a Win2K3 Server, with just a C:, D: and E: "drive", and you might know nothing<br> of the specifics of storage.<br> This is not the way that you will have fun with large, and/or active, databases.<br> Because, always try to sperate tabledata, indexes, transaction logfiles, (and tempdb) on their own diskvolumes,<br> that is, seperate "spindels".<br> Otherwise you might encounter very serious Disk IO problems.<br> <br> While it is quite easy "to get the evidence" of IO problems (see for example section 1), to "repair" the situation<br> often is quite difficult.<br> <br> Don't get me wrong: for smaller SQL Server database, you probably will not have any difficulties at all.<br> And besides, ESX has all sorts of very neat tricks, like online moving a VM to another host, and much more.<br> <br> But for a very large, very active database, take care. You might consider a dedicated physical machine.<br> <br> By the way, the general consensus "in the IT community" just <U>seem</U> to support the above view.<br> (please note the word "seem" here).<br> Anyway, at occasions that I talk with Microsoft engineers on this subject, they always say that it's indeed <I>"Not Done"</I>.<br> <br> Note: I certainly like virtualization. If you are on good terms with the sysadmins en storage people, most of the time<br> things will work out OK. For example, Oracle on AIX lpars, or HPUX vpars, or DB2 on Z lpars etc... etc..,<br> works (in general) OK.<br> I must say that I am a bit dissapointed by a couple of bad experiences with really large SQL Server environments<br> on VMWare. I always seem to have problems with Windows Virtualization and large databases. For me, it does not work well.<br> I always have Disk IO problems and/or cpu utilizaton problems and/or Memory problems and/or network problems,<br> where Disk IO is a prominently number "1" cause of low performance.<br> So, it's certainly not an "absolute truth". I only tell you to be carefull, and try to create good test scenario's.<br> <br> If you must use a VM (for example, because of a <I>very</I> strict company policy), sit down with the sysadmin<br> and storage admin, and make your resource requirements very clear (and make them your friends as well).<br> <br> <br> <h2>Chapter 3. In case of a large, very "active" database, always choose a 64 bit OS and 64 bit SQL Server edition.</h2> This is ofcourse quite obvious. Even if we just <I>only</I> consider the memory that a 64 bit<br> SQL Server can use, compared to a 32 bit edition, the difference is astonishing.<br> <br> On 32 bit systems, you are limited to 4GB of memory. You <I>could</I> put the socalled AWE feature at work, will would allow<br> for a maximum of 64GB cache. But that is not computational memory, it's just for caching.<br> <br> A 64 bit SQL Server, on the other hand, can directly address 1,024 gigabytes of physical memory, <br> so the amount of data SQL Server can access for cache and computational memory, is thus much, much larger.<br> It will pay off in almost all actions that SQL Server perfoms.<br> <br> Ofcourse, you need 64bit hardware to implement the 64 bit OS and 64 bit SQL Server editions.<br> <br> So, unless you are restricted by hardware or licensing issues, you cannot really speak of a choice here:<br> For larger and/or very active databases, you <I>should always</I> go for the 64 bit environment.<br> <br> True, this is all very trivial and obvious. Sorry for that.<br> <br> <br> <h2>Chapter 4. Indexes and Index tuning.</h2> You surely know what indexes are, and why they need to exist, certainly for larger tables.<br> <br> <h3>4.1 Short recap on indexes </h3> Here are a few facts (just take them for granted), which will be explained later in more depth.<br> For the "traditional" relational SQL Server tables, two types of indexes exist: <B>clustered index</B> and <B>non-clustered index</B>.<br> <br> <ol> <li>A table without a "clustered" index, is called a "heap". Essentially, the table rows are then not ordered by some "key"<br> (this key is one or more columns of the table).<br> Now, this table could have one or more "non-clustered indexes", but it's still a heap.<br> <br></li> <li>If a table has a clustered index, ONLY THEN the rows of the table are ordered by the key of that clustered index. So, actually really<br> the rows in the pages are sorted by that key. Thus, a table can only have ONE clustered index, because you can physically "sort"<br> the table rows <B>in one way only</B> (and not by another key at the same time: that's not possible).<br> Preferrably, the key of the clustered index is a table column of a nummeric datatype, that increases with each row added.<br> But, a character based dataype is often used too, like for example a "social security number".<br> <br></li> <li>A table may have one or more "non clustered" indexes. A non clustered index, will not order the physical rows<br> of a table (like a clustered index does). So, a table might have one clustered index (only one), and multple non-clustered indexes.<br> <br></li> <li>A non-clustered index is just an external object, looking like a "mini representation" of the table. It has (leaf)pages containing rows,<br> each of which has the choosen key of the table (one or more columns of that table), and a pointer to the corresponding<br> tablepage which ofcourse contain all the data. That is one reason why an index speeds up searches in tables.<br> Such an index looks logically like a tree: it has a root page containing pointers to an intermediate level with pages,<br> where those pages point to the leaf level of pages. Those pages then, contains the actual keys and pointers to the <B>table</b> pages.<br> When a query uses such an index, the <I>tree is traversed</I>, and the table row is then quicly found.<br> <br></li> <li>When a table has no indexes, a <I>full table scan</I> is done, meaning starting at the first page, all the way down,<br> unitl the sought after row is found. That's why, with larger tables, indexes are indispensible.<br> <br></li> <li>A clustered index is somewhat "special" (if you happen to be familiar with Oracle, it looks like a IOT).<br> What we have said in point 4, largely applies to a clustered index as well.<br> Except that in this case, <I>the leaf level are the table pages themselves (!)</I>.<br> <br> At the moment a clustered index on a table is created, the table rows get sorted (on the choosen key of the index).<br> When that's done, the index leaf level, just is the same as the <B>table pages.</B><br> <br> To put it in simple words: A clustered Index is the ordered table itself.<br> <I>Note: it's almost true, except for the rootlevel and any optional intermediate level.</I><br> <br> <li>Only when a clustered index is defined for a table, the table is not a "heap" anymore. The tablerows get sorted<br> on the choosen key.<br> By the way, if a "Primary Key" is defined for a table, SQL Server will per default create an associated<br> unique (clustered) index. You know probably that a Primary Key column in a table, must have all unique values.<br> So, each row in the table <B>is unique</B> with respect to that particular column (or set of columns).<br> The above rule, is actually enforced by the unique index. A few lines above, I told you that a clustered index<br> will be created. In practice, you almost always will do that. But actually, a "unique" index is already sufficient.<br> <br> Don't worry now. There are just two index types (clustered and non-clustered). But an index can be defined as unique, if the choosen key, only takes on unique values. So, even a non-clustered index can be defined as unique.<br> <br></li> <li>If a primary key is defined for a table, then you automatically have your (one and only) clustered index as well.<br> But you may add aditional non-clustered indexes. If you are certain, that a relevant number of queries<br> have in the "where clause", colums that are not in the clustered index key, you probably consider creating<br> one or more (a well balanced number) of non-clustered index(es).<br> It's a bit of an art. You surely can have a situation where the addition of a non-clustered index helps tremendously.<br> But if you have too many (useless) indexes, it works counter productive.<br> You know, any Insert, Delete, and Update SQL statement, alters a row (or more rows) in the table, and all associated<br> indexes must be updated as well, to reflect the new situation. </ol> <br> All objects in SQL Server have an "Object ID" (object_id). This holds for tables, indexes and other objects.<br> <br> Once indexes are defined on a table, those indexes have an "index id" (index_id or ind_id) as well.<br> <br> You know about the "close" relationship between tables and indexes. For example, the leaflevel of pages of a<br> clustered index, just are the table pages.<br> There are a quite a few dynamic management views and functions that show the properties of indexes and tables.<br> If a table has indexes, the following index_id's hold for the table and it's indexes<br> <br> <B>A: table has no clustered index</B><br> <br> index_id=0 :index id of the heap (the table itself)<br> index_id>1 :index id's of the non-clustered indexes (like 1, 2, 3 etc..)<br> <br> <B>A: table has a clustered index</B><br> <br> index_id=1 :index id of the clustered index (the table itself)<br> index_id>1 :index id's of the non-clustered indexes<br> <br> <I>Note: in some older views (from former versions) the "index_id" is listed as "indid".</I><br> <br> <h3>4.2 Dynamic management views and functions: </h3> The following views and functions will help us a lot in determining many properties of indexes, like the number of rows in the index,<br> the number of pages, fragmentation level, if it might be a hotspot, or contrary, is not uses much etc.. etc..<br> <br> <I>This is what you likely want to know in a quick and easy manner:</I><br> <br> => If an index is a "hotspot", then that's good because it's evidently used much.<br> But, if you have multiple "hotspots" <B>in the same file</B>, then you might consider moving an hotspot to another file.<br> <br> => Also, since indexes "mutate" as well, you want to know how fragmented they are, and that might trigger you to<br> reorganize or even rebuild an index (maintenance of an index).<br> <br> => If an index <I>is (almost) not used at all</I>, you might want to remove it.<br> <br> =>You also want to easily see how many rows an index has. The larger indexes are probably the more important ones,<br> and if they are fragmented, you can create lists of indexes on which you want to do maintenance.<br> Besides, then you also know which rebuilds of which indexes will hit performance most during a rebuild.<br> <br> <B>The following views an functions are important:</B><br> <br> <ol> <li><B>sysindexes (view):</B> An old friend from former SQL server versions. It's still available in 2005/2008.<br> Contains one row for each index (or heap) with many important properties. Often it is joined with "sysobjects", in order<br> to get the associated tablenames as well.<br> <br></li> <li><B>sys.dm_db_index_operational_stats (function):</B> Since 2005 and up. Contains one row for each index,<br> and aggregates the number of leaf and non-leaf inserts, updates, deletes along with page latch statistics.<br> <br></li> <li><B>sys.dm_db_index_physical_stats (function):</B> Shows per index the fragmentation and allocation information.<br> <br></li> <li><B>sys.dm_db_index_usage_stats (view):</B> Shows per index, statistics of how frequently an index is accessed,<br> as well as how many times it is accessed.<br> <br></li> <li><B>sys.dm_db_missing_index_details (view)</B> contains records of possible indexes that the optimizer might <br> be able to take advantage of, but that not exist within the database. So, they might be candidates to create.<br> <br></li> </ol> <br> <B><U>4.2.1 The "sysindexes" VIEW:</U></B>:<br> <br> Let's take a look to a usefull query, using this "old" systemview.<br> Let's try the following. It shows all tables with all their indexes, with their index_id's and number of rows.<br> The list is sorted by the number of rows, listing the largest first.<br> <br> <font face="courier" size=2 color="black"> USE YOUR_DATABASE_NAME -- for example: USE SALES<br> GO<br> <br> SELECT<br> substring(sysobjects.name,1,40) AS TABLENAME, <br> substring(sysindexes.name,1,40) AS INDEXNAME, <br> sysobjects.id, sysindexes.indid, sysindexes.groupid,sysindexes.rows<br> FROM sysobjects, sysindexes<br> WHERE sysobjects.id=sysindexes.id<br> ORDER BY sysindexes.rows desc<br> GO<br> <br> In the figure below, you can see some example output:<br> <br> <img src="sql7.jpg" align="center"/> <br> <br> <font face="arial" size=2 color="black"> <I>Note: In the actual real output, you will see the systemviews as well.</I><br> <br> Actually, it's a neat list. You can see all tables with their associated indexes.<br> The indexes which are a clustered index, have an "indid" of "1", like "pkbob_price"<br> Remember that the (leafpages of) clustered indexes are actually the tablepages themselves.<br> <br> Also note that the table called "VALUE" is a "heap", with an indid of "0". It does not have a clustered index.<br> Because this table is a heap, it is listed as "null" at the indexname column. But the table has 5 non-clustered indexes,<br> and thats why you see the tablename repeated.<br> <br> It's also nice to have a list of the number of rows of the indexes.<br> <br> So, this query shows you the tablename, with all index names associated with that table,<br> as well as the object_id of the table, the index_id of those indexes, and the number of rows in all heaps,<br> clustered indexes and non-clustered indexes.<br> <br> <br> <B><U>4.2.2 The "sys.dm_db_index_operational_stats" FUNCTION:</U></B>:<br> <br> A function is not the same as a view, ofcourse. But, you use them not in a much different way as a view.<br> In both case you make statements like <B>"SELECT .. FROM [view|function ].</B><br> Only, a function often expects "parameters" like in "functionname(parameters), where the parameters might be<br> an "object_id", "index_id", or a "database_id" etc..<br> <br> - If you specify all parameters, it usually means you need information of one object only.<br> - In many cases, parameters may take on "NULL", which means you get information of more objects, or even all objects.<br> <br> Let's see how we can use the "sys.dm_db_index_operational_stats" function.<br> A short description of this function is: <I>Contains one row for each index,<br> and aggregates the number of leaf and non-leaf inserts, updates, deletes, along with page latch statistics.</I><br> <br> That's very interesting, because here you are able to extract information whether an index is much used or not.<br> Syntax of the function:<br> <font face="courier" size=2 color="black"> <br> sys.dm_db_index_operational_stats (<br> { database_id | NULL | 0 | DEFAULT }<br> , { object_id | NULL | 0 | DEFAULT }<br> , { index_id | 0 | NULL | -1 | DEFAULT }<br> , { partition_number | NULL | 0 | DEFAULT }<br> )<br> <br> Here is an example query:<br> <br> <B> SELECT database_id, object_name(object_id), index_id, leaf_insert_count, leaf_delete_count,<br> nonleaf_delete_count, nonleaf_update_count FROM <I>-- many more columns could have been chosen</I><br> sys.dm_db_index_operational_stats(DB_ID('eximius_production_data'), OBJECT_iD('VALUE'),NULL,NULL)</B><br> <br> <I>Meaning that from the 'eximius_production_data' database, you want operational stats of all indexes<br> associated to the 'VALUE' table.</I><br> <br> <br> <font face="arial" size=2 color="black"> <img src="sql9.jpg" align="center"/> <br> <br> Now, suppose your production database has a database_id of '5' (easy to find with "select database_id, name from sys.databases")<br> Now suppose you want operational stats of all indexes of all tables, then use this <B>really nice query</B>:<br> <font face="courier" size=2 color="black"> <br> <B>SELECT database_id, object_name(object_id), index_id, leaf_insert_count, leaf_delete_count,<br> nonleaf_delete_count, nonleaf_update_count FROM <br> sys.dm_db_index_operational_stats(5, NULL,NULL,NULL)</B><br> <br> <font face="arial" size=2 color="black"> Notes:<br> - Ofcourse, you can extend the query with a clause like "ORDER BY leaf_insert_count" etc..<br> - Also make sure you try <B>"SELECT * FROM sys.dm_db_index_operational_stats(5, NULL,NULL,NULL)"</B>, just to see what<br> other columns of information can be extracted.<br> <br> In the above two example queries, we focused on leaf_insert_count, leaf_delete_count etc..<br> But this function will show you much information on "locks" and "contention" as well !<br> To find out if a certain index is "used much, or not", you can compare the "count" columns of the indexes.<br> But there are many "wait" columns too, giving clues to if a certain index is a "hotspot".<br> For your database, try the function again, but this time also include the following columns:<br> <br> <ol> <li><B>row_lock_count:</B> Cumulative number of row locks requested.</li> <li><B>row_lock_wait_count:</B> Cumulative number of times the Database Engine waited on a row lock.</li> <li><B>row_lock_wait_in_ms:</B> Total number of milliseconds the Database Engine waited on a row lock. </li> <li><B>page_lock_wait_in_ms:</B> Total number of milliseconds the Database Engine waited on a page lock. </li> </ol> <br> Then make lists of of all indexes, and compare the values among those indexes. This <I>will</I> give<br> you a good idea about hotspots. If you see multiple indexes with longer waits, it might be an indication<br> to move one or two to another filegroup.<br>. <br> <B><U>4.2.3 The "sys.dm_db_index_usage_stats" VIEW:</U></B><br> <br> You should use this view complementary to the function in 4.2.2.<br> The function from 4.2.2, shows you "waits" and leaf_insert_count, leaf_delete_count etc..<br> But with this view, will see differentation between <B>user_scans, user_lookups, user_updates, last_user_seek,last_user_scan,last_user_lookup,last_user_update etc..</B><br> So, it's really easy to find out if an index is usefull or not. If the "last_" columns only show old date/times,<br> then obviously, the index is not used. <B>It may even be a candidate to delete it.</B><br> But not so fast. Maybe the "statistics" are so lousy, that the optimizer does not consider it.<br> But if you are sure the "statistics" are up to date, and the "user_" and "last_" show small or old values,<br> then consider deletion of that index.<br> <br> Note that this view registers statistics of all indexes of all databases. So, probably you want to use<br> a "WHERE" clause that specifies your database (id) of interest.<br> Also, the query might take so time to run in a very large database, so first experiment a bit on testsystems.<br> <br> Since its a view, use "SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id=<the_database_id>".<br> From that, browse the columns that are interesting, and adjust your query accordingly, possibly with clauses like<br> "user_updates>n", or "ORDER BY user_updates" etc..<br> <br> You really need to play with this view a bit, to appreciate the information you can get.<br> <br> <br> <B><U>4.2.4 The "sys.dm_db_index_physical_stats" FUNCTION:</U></B><br> <br> You notice the "physical_stats" in the name of this function? Indeed, this function will reveal to us<br> how index pages are filled with rows, and what their average fragmentation level is.<br> <br> <img src="sql10.jpg" align="center"/> <br> <h3>4.3 How to move a hotspot index to another Filegroup: </h3> Important:<br> <ol> <li>Make sure you have scripted your objects, that is, have the create scripts of all objects.</li><br> <li>Moving large indexes, cost a lot of performance and will take time. With the Enterprise Edition, theoretically<br> you can do it "ONLINE", but, for example, you don't want to move a 700 million row index while the users are busy.</li><br> </ol> <br> Personally, I think that moving objects like tables and indexes to other tablespaces, is quite easy in Oracle.<br> Ofcouse, the methods exist in SQL Server too, but here it's somewhat more elaborate.<br> <br> Anyway, if you move an index from one filegroup to another filegroup, there might be a mean rattlesnake in the grass.<br> Essentially, moving an existing index, means dropping it and then recreating it.<br> Now, if this index supports some Constraint, like a Primary key, or PK-FK relations, you must enable them again!<br> It is easy to forget this, because moving an index "looks" like a single action. It is, as long there are no<br> constraints involved.<br> Although, trying to explicitly dropping an index (with the DROP INDEX statement) that supports a Primary key, will be denied<br> by the Database Engine. But, other statements (like ALTER INDEX) will work.<br> <br> In reality, before you do anything, make sure you script the database. That means that SQL Server will script<br> all create statements with respect to tables, indexes, constraints, triggers, etc.. etc..<br> This way, you just have an ascii file with all create statements, and if you need it, you can easily retrieve<br> for example, an index create statement etc.. without that you need to "remember" which columns were involved. It's easy to do that: just browse around a bit in SQL Server Management Studio (SSMS).<br> (Just rightclick your database -> choose Tasks -> choose Generate Scripts)<br> <br> Note: Many professional systems, have some sort of repository, describing all objects, including the TSQL create statements.<br> <br> <B>Now, how to move an table and or an index to another filegroup?</B><br> <br> Let's review in a few simple examples, how indexes are created on an existing table.<br> <br> <font face="courier" size=2 color="black"> Lets make a simple example table:<br> <br> CREATE TABLE dbo.Customers<br> (<br> Cust_id int NOT NULL,<br> Cust_name varchar(20) NOT NULL,<br> Address varchar(30),<br> City varchar(20),<br> Country varchar(20)<br> ) <br> ON FG_DATA -- The filegroup FG_DATA for storage of tables<br> GO<br> <br> - Clustered:<br> <br> CREATE CLUSTERED INDEX indx_employee_emp_id ON EMPLOYEE(EMP_ID)<br> <br> <br> - non-clustered:<br> <br> CREATE NONCLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME)<br> ON FG_INDEX -- The filegroup FG_INDEX for storage of non-clustered indexes<br> <br> Or the Index Create statement in a slightly generalized form:<br> <br> CREATE [CLUSTERED | NONCLUSTERED] INDEX <index_name> ON TABLENAME(column1, column2,...) [ON filegroup_name]<br> <font face="arial" size=2 color="black"> <br> Note that I did not use the "ON FG_INDEX" clause with the clustered index create statement.<br> I <I>could</I> have done that, but that would imply that I "moved" the table to that filegroup.<br> Again, the table (in simple words) is actually the same as the clustered index.<br> <br> So, we can immediately conclude the following:<br> <br> <B>1. How to "move" a table with a clustered index to another filegroup:</B><br> <br> DROP the clustered index. Create the Clustered Index again with Filegroup clause pointing to the right filegroup.<br> <br> <B>2. How to "move" a clustered index to another filegroup:</B><br> <br> Ofcourse, it's the same as above.<br> <br> You cannot move indexes supporting a unique or primary key constraint, using a DROP statement.<br> As we will see later on, to move these indexes, we must use the CREATE INDEX statement with the (DROP_EXISTING=ON) option.<br> <br> <B>3. How to "move" a non-clustered index to another filegroup:</B><br> <br> DROP the non-clustered index. Create the non-Clustered Index again with Filegroup clause pointing to the right filegroup.<br> <br> The points 2 and 3 are great, if you would have the original CREATE statements of those indexes.<br> But there exists another method too. This is by using the "CREATE INDEX .. (DROP_EXISTING=ON).. ON [filegroup_name]" statement <br> <br> <B>4. Alternative method for "moving" a clustered or non-clustered index to another filegroup:</B><br> <br> As a generic example for using the CREATE INDEX .. (DROP_EXISTING=ON) statement, take a look at the following syntax:<br> <br> <font face="courier" size=2 color="black"> CREATE [NON]CLUSTERED INDEX INDEX_NAME<br> ON TABLE_NAME(column1, column2,...)<br> WITH DROP_EXISTING <br> ON [filegroup_name] <br> <br> <font face="arial" size=2 color="black"> If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition (which columns) is not altered in any way,<br> the index is dropped and re-created, and will preserve the existing constraint. <br> If you want a list of Primary, Unique, and Foreign key constraints, you might want to play with the following queries:<br> <br> <font face="courier" size=2 color="black"> select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS<br> select * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE<br> select * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS<br> <br> SELECT substring(object_name(constid), 1, 40) AS FK,<br> substring(object_name(fkeyid), 1, 40) AS "Referencing Table",<br> substring(object_name(rkeyid), 1, 40) AS "Referenced Table"<br> FROM sysreferences<br> ORDER BY object_name(rkeyid)<br> <br> <font face="arial" size=2 color="black"> <br> <br> <h3>4.4 Quick check on the effectiveness of your indexes: </h3> In section 4.2.3, we have touched the "sys.dm_db_index_usage_stats" Dynamic Management View.<br> This view shows us many interresting facts like "user_scans", "user_lookups" etc.., so it gives us a good idea<br> on the usage, or the effectivity, of an index.<br> <br> - In general, if an index has a relatively high number of "reads", compared to the "writes", that is a clue<br> that the index is used much. So, it's probaby an effective index.<br> So, if the "total Reads" > "total Writes", it's a good index.<br> <br> - Also, if an index has a relatively high number of "writes", compared to the "reads", that is a clue<br> that the index is NOT used much. So, it's probably an ineffective index.<br> If there are little reads, but many writes, the system is busy updating the index, without that users are using it.<br> You know that writes to an index means updating it. Reading an index means that a query is using it.<br> So, if the "total Writes" > "total Reads", the index only spills performance, and it's not a good index.<br> <br> Here is a handy query that produces a list of the indexes in a Database, including the <B>Reads from</B>, and <B>writes to</B>, the indexes.<br> <br> <font face="courier" size=2 color="black"> SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,<br> user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],<br> user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]<br> FROM <B>sys.dm_db_index_usage_stats</B> AS s WITH (NOLOCK)<br> INNER JOIN sys.indexes AS i WITH (NOLOCK)<br> ON s.[object_id] = i.[object_id]<br> AND i.index_id = s.index_id<br> WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1<br> AND s.database_id = DB_ID()<br> AND user_updates > (user_seeks + user_scans + user_lookups)<br> AND i.index_id > 1 -- not the heap or clustered indexes<br> ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;<br> <font face="arial" size=2 color="black"> <br> Note how this query only uses the "sys.dm_db_index_usage_stats" Dynamic Management View. <br> <h3>4.5 How to Reorganize and Rebuild Indexes: </h3> <B>4.5.1 How to detect Index Fragmentation:</B><br> <br> Especially, the dynamic management function "sys.dm_db_index_physical_stats" can be of help in determining<br> if an index is too much fragmented or not.<br> <br> The function need a number of parameters, which might be left as "null".<br> The more parameters you specify (as not being "null"), the more specific the output will be.<br> <br> In particular, there are three very usefull ways to use the function:<br> <br> (1) Get the details (a list) of the fragmentation levels of all indexes in a certain Database.<br> (2) Get the details of the fragmentation levels of all indexes of just one specific Table.<br> (3) Get the details of the fragmentation level of just one specific index of just one specific Table.<br> <br> Here are a few examples:<br> <font face="courier" size=2 color="black"> <br> SELECT index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent<br> FROM sys.dm_db_index_physical_stats<B>(DB_ID('SALES'), OBJECT_ID('dbo.EMPLOYEE'),null,null,'DETAILED')</B><br> <br> SELECT SUBSTRING(OBJECT_NAME(object_id),1,30) AS NAME, index_id, SUBSTRING(index_type_desc,1,20) AS TYPE,<br> avg_fragmentation_in_percent, avg_fragment_size_in_pages page_count<br> FROM sys.dm_db_index_physical_stats<B>(DB_ID(N'SALES'), NULL, NULL, NULL , 'DETAILED')</B><br> <br> <font face="arial" size=2 color="black"> The first query, shows the fragmentation (column "avg_fragmentation_in_percent") of the indexes of the EMPLOYEE table only.<br> <br> The second query, shows fragmentation information of all indexes (of all Tables) that exist in the SALES database.<br> <br> Note that the column "avg_fragmentation_in_percent" in the output, shows you the relevant information.<br> In section 4.2.4, you can find a real example, with example output, of using this function.<br> <br> According to Microsoft documents, you should Rebuild indexes when the average fragmentation is higher than, say, 25 to 30%.<br> You might consider Reorganizing indexes when the average fragmentation is between, say, 15 to 25%.<br> <br> For the smaller indexes, don't expect high improvements from rebuilding or reorganizing indexes.<br> For larger indexes, the improvement can be very significant.<br> <br> <B>4.5.2 How to Reorganize and Rebuild Indexes:</B><br> <br> - Reorganizing, defragments only the leaf level of clustered and nonclustered indexes on tables. The same pages are used again.<br> Since a fill factor can be specified, it's likely that empty pages will result by this "compaction". These are removed, and thus providing additional available disk space.<br> The fill factor, as the name already implies, specifies "how full" a page should be filled, like for example "70" (70%) or "80" (80%).<br> <br> - Rebuilding an index actually drops the index and re-creates a new one. Since this action means <I>completely rebuilding</I> a new index,<br> all levels (leaf level, intermediate levels, root level) are recreated again, and all fragmentation is removed.<br> In this process, you reclaim disk space, since all pages are build again using the specified "fill factor" setting.<br> <br> Usually, rebuilding an index is more resource intensive than reorganizing an index.<br> Reorganizing is automatically done "online", thus while sessions may access the table and index.<br> Rebuilding can be done online or offline. If done offline, locks will block sessions for the time the index is rebuild.<br> With the Enterprise Edition, using the "ONLINE=ON" clause, you can rebuild indexes online.<br> Still, with very large indexes, it's really best to rebuild them during the times of least activity in the Database.<br> <br> Here are a few examples on how to rebuild or reorganize indexes:<br> <br> First a warning. You can explicitly DROP an index, and CREATE it again, assuming you had the original CREATE statement.<br> In most cases, the Database will not execute the statement if the index supports (or "is") a Primary Key or Unique constraint.<br> But you still must be carefull, in how far the index supports any constraint at all. You have to investigate that first.<br> So, it's best to NOT to "DROP" and "CREATE" an index, unless you know the details of your constraints.<br> <br> But, when using the "ALTER INDEX .. REBUILD" and "DBCC DBREINDEX()" commands, you are pretty save.<br> <br> Thus, you can use two types of commands to Rebuild an Index: the "ALTER INDEX" statement and the "DBCC DBREINDEX()" statement.<br> The DBCC command is more SQL Server 7/2000 "style", but it's still valid in 2005 and 2008.<br> Just take a look at the following examples. How to deal with <B>all</B> indexes of <B>all</B> tables in a database, is the subject of the next section.<br> <br> The examples below are <I>very simplistic</I>. You need to read Books Online (BOL), or search the internet, to find all clauses and options<br> that you can use with the ALTER INDEX and DBCC REINDEX commands.<br> <br> <font face="courier" size=2 color="black"> ALTER INDEX IDX_Employee_EMPID ON Employee REBUILD <I>-- only rebuild the index "IDX_Employee_EMPID"</I><br> <br> ALTER INDEX ALL ON Employee REBUILD <I>-- rebuild all indexes of the EMPLOYEE table</I><br> <br> ALTER INDEX IDX_Employee_EMPID ON Employee REBUILD WITH (FILLFACTOR = 80) <I>-- only rebuild the index "IDX_Employee_EMPID" with FILLFACTOR=80</I><br><br> <br> So, "ALTER INDEX ALL Table_Name REBUILD WITH (FILLFACTOR = n)" takes care of all indexes of a certain table.<br> <br> DBCC DBREINDEX(EMPLOYEE,'',80) <I>-- rebuild all indexes of the EMPLOYEE table with a FILLFACTOR=80</I><br> <br> So, "DBCC DBREINDEX(Table_Name,'',Fillfactor)", takes care of all indexes of a certain table.<br> <br> <font face="arial" size=2 color="black"> Question:<br> <br> Maybe this is a difficult question. It's certainly a very interresting question.<br> If needed, search Books Online (BOL) and/or the Internet for answers.<br> <br> Suppose a table has a clustered index, and several non-clustered indexes.<br> What happens to the non-clustered indexes, if you rebuild the clustered index?<br> <br> <B>4.5.3 How to "dynamically" generate index rebuild statements of all indexes in a Database:</B><br> <br> If you want to "dynamically" generate the rebuild statements for all usertables in a Database,<br> we can use a looping construct, called a cursor.<br> <br> <font face="courier" size=2 color="black"> -- A loop generating DBCC statements:<br> <br> <font face="courier" size=2 color="black"> set nocount on<br> DECLARE @TableName varchar(255)<br> <br> DECLARE TableCursor CURSOR FOR <br> SELECT table_name FROM information_schema.tables <br> WHERE table_type = 'base table' <br> <br> OPEN TableCursor <br> <br> FETCH NEXT FROM TableCursor INTO @TableName <br> WHILE @@FETCH_STATUS = 0 <br> BEGIN <br> SELECT 'DBCC DBREINDEX('+@TableName+','+''''',80)' -- asssuming a fill factor of 80<br> FETCH NEXT FROM TableCursor INTO @TableName <br> END <br> <br> CLOSE TableCursor <br> <br> DEALLOCATE TableCursor<br> <br> -- A loop generating ALTER INDEX statements:<br> <br> set nocount on<br> DECLARE @TableName varchar(255) <br> <br> DECLARE TableCursor CURSOR FOR <br> SELECT table_name FROM information_schema.tables <br> WHERE table_type = 'base table' <br> <br> OPEN TableCursor <br> <br> FETCH NEXT FROM TableCursor INTO @TableName <br> WHILE @@FETCH_STATUS = 0 <br> BEGIN <br> SELECT 'ALTER INDEX ALL ON '+@TableName+' REBUILD WITH (FILLFACTOR=80)'<br> FETCH NEXT FROM TableCursor INTO @TableName <br> END <br> <br> CLOSE TableCursor <br> <br> DEALLOCATE TableCursor<br> <font face="arial" size=2 color="black"> <br> <br> <h2>Chapter 5. How to determine if you have too low CPU power.</h2> Again, just like in chapter 1, <B>System Monitor</B> (or Performance Monitor), and the <B>dynamic management views</B> (dmv's) can learn us a lot.<br> <br> It's important to differentiate here, between a <I>"real cpu problem"</I>, meaning that for the workload,<br> you just have too little CPU power. But it can be a bit confusing, because it can also be that you only have an "apparent cpu problem",<br> because of <B>waits</B> of some sort.<br> <br> Let's first take a look at what we can discover using Performance Monitor (perfmon).<br> <br> <h3>5.1 Taking a quick look Using Perfmon:</h3> This is going to be a bit of a trivial section, I am afraid.<br> <br. As you can see in the example below, you can add counters from the "processor" object.<br> Some obvious counters are <B>"Processor\%Priviledge Time(_Total)", "Processor\%User Time(_Total)", and "Processor\%Idle Time(_Total)"</B>.<br> <br> One other very important counter, is <B>not found</B> under the "Processor" object. Instead, select the "System" object, and select<br> the <B>"System\Processor Queue Length" </B> counter.<br> <br> Please see section 1.2 for a short explanation on "objects", "counters", and "instances".<br> There are ofcourse many other interresting counters like "%DPC Time" etc.., but these are not <I>that</I> "relevant" for "our quick look".<br> <br> A short description of the above counters is:<br> <br> <B>- %Idle Time:</B> Shows the %time that the processor(s) was idle during the sample interval.<br> <B>- %Priviledge Time:</B> Shows the %time that the processor(s) was running Kernel Mode code during the sample interval.<br> <B>- %User Time:</B> Shows the %time that the processor(s) was running User Mode code (non kernel) during the sample interval.<br> <B>- %Processor Time:</B> Is almost the same as (%Priviledge Time + %User Time).<br> <B>- Processor Queue Length:</B> This indicates the number of threads in the processor queue. It may be considered to be "high",<br> if on average it's much more than 2 x #cpu's in your system.<br> <br> With all those counters, some occasional "spikes" are no big deal. Only if you see structural high values (except for "Idle Time"),<br> on average, or for a considerable amount of time (say, during a "batch"), you might have a cpu botlleneck.<br> <br> 1. One very trivial remark is this: if the "Processor:Idle Time(_Total)" is very high <I>most of the time</I>, you do not have<br> a CPU pressure at all on your system. Or, what is the same, if %Processor Time is low <I>most of the time</I>,<br> you do not have a CPU pressure on your system.<br> <br> 2. If <B>on average</B>, you system shows about 40, 50, 60 %Processor time, I would say it's nicely at work.<br> Because, suppose that the cpu's were idle <I>all the time</I>, then that's not good either. It would be a waste of cpu power.<br> <br> 3. Now, what if the cpu's show a %Processor time which hovers around 90% <I>most of the time</I>? That should certainly get our attention.<br> <br> One of the most important questions following point 3) is, is it really a CPU bottleneck, or are those observations<br> also caused by "some deeper technical reason"?<br> <br> 4. If the cpu's show a "%Processor time" which is high <I>most of the time</I> and "Processor Queue Length" is considerably large too,<br> then you have a strong indication of a structural CPU pressure.<br> <br> Although it all seems "obvious", do no jump to conclusions yet.<br> I think that after reading the material in chapter 9, we will appreciate that statement somewhat more.<br> <br> <img src="sql15.jpg" align="center"/> <br> <br> If you use perfmon and measure the above metrics for a representable amount of time, and you <I>indeed</I> find<br> high values on average, it's <I>quite likely</I> you have a bottleneck "somewhere". And indeed, it's likely to be the cpu's.<br> But what to think of this example. Suppose you have a Database Server, and a number of application Servers.<br> When some large batch starts, many application components on the Application Servers, all starts tasks on the<br> Database Server at the same time, and all keeps running until the batch finishes. Maybe thats why we see such a high cpu pressure.<br> It could be true that this is "as designed", and we are too low on cpu power. Or something else is not right.<br> We always need a helicopter view so to speak, on the system as a whole.<br> <br> So, it's often too difficult to reach well founded conclusions, using Performance Monitor alone.<br> <br> That's why you also need other information, and at least take a look at the dynamic management views as well.<br> <br> <h3>5.2 Using some dynamic management views:</h3> Again, the DMV "sys.dm_os_wait_stats" can give us valuable pointers to existence of possible "CPU pressure".<br> When a session is "ready for some work", it will first enter the "runnable queue". The longer this queue is,<br> the more we may presume that cpu pressure is actually really true, since a longer queue length implies that<br> the cpu cannot keep up sufficiently with the demands.<br> As a session does work, but then have to wait on some "resource" (like pages that must be fetched from disk),<br> it will be put in a "waiters queue", until conditions have arived that makes it runnable again (put in the runnable queue).<br> <br> The time waiting in the runnable queue for CPU, is shown as "Signal Waits".<br> The time waiting for a resource is shown as "Resource Waits".<br> <br> The following query will show you a grand total of the percentage of Signal Waits and Resource waits,<br> and thus allows us to compare the two.<br> <br> If the total of "%signal waits" is highest, it's a reasonable pointer to suspect CPU pressure.<br> If the total of "%Resource waits is highest, it's a reasonable pointer to suspect overall poor IO, or excessive locking behaviour.<br> <br> Again, to draw conclusions on this alone, is not a good idea ! Also take a look at section 5.1.<br> <br> <font face="courier" size=2 color="black"> Select signal_wait_time_ms=sum(signal_wait_time_ms)<br> ,'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))<br> ,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)<br> ,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))<br> From sys.dm_os_wait_stats<br> <br> select top 20 <br> st.objectid, st.dbid,<br> total_worker_time/execution_count AS AverageCPUTime,<br> CASE statement_end_offset <br> WHEN -1 THEN st.text<br> ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)<br> END AS StatementText<br> from <br> sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st<br> ORDER BY AverageCPUTime DESC<br> <br> <font face="arial" size=2 color="black"> This section is not ready yet. I am thinking on what to present in chapter 9, probably finish that first,<br> and then return to this section.<br> So please continue with the other chapters. Thanks !<br> <br> <h2>Chapter 6. Allocation Unit and Partition Alignment.</h2> If you are involved in a new project, designing an architecture for a large and/or very active database,<br> the design/implementation of storage for your database is very important.<br> <br> You always should seperate tabledata (or clustered indexes), and non-clustered indexes,<br> and the files of the transactionlog (and tempdb also), on different filesystems (E:, F:, G: etc..) on different<br> diskvolumes (actually, different independent spindels).<br> This is to ensure performance. At the same time, you need a High Availabily solution for your database files,<br> like some RAID implementations.<br> I presume that the upper is know to you. In chapter 7, we are going to deal with that subject.<br> <br> In this chapter however, we mean something different: here we are going to spend a few words on the "allocation unit"<br> and "partition alignment". This subject is not too exiting, and don't expect miracles from it.<br> <br> In the following, we will make a certain abstraction. Be warned though: for your specific types of storage, the following<br> maybe <B>does not apply at all!</B> That's why you need to talk with people who understand your type of storage.<br> <br> <br> <B><U>Allocation Unit:</B></U><br> <br> Disk sectors are almost always 512 bytes in size. The <B>allocation unit</B>, or blocksize on disk (not the SQL server blocksize!)<br> is determined when you format the disk. Here, you often can choose values like 2K, 4K, 16K, 32K, 64K, and sometimes even larger.<br> Now, the question may arise: is there an optimal allocation unit for SQL Server?<br> The fundamental pagesize in SQL server is 8K. Secondly, SQL Server often 'read ahead' in 64K 'chunks' (extends).<br> <br> Two things here:<br> This question relates to the RAID implementation you have choosen, like RAID5 or RAID10.<br> In this case, under the hood, the chosen stripesize is of importance, and determines what you should<br> should take as a suitable allocation unit.<br> <br> 1. If you use such a RAID implementation, first the choosen "stripe size" is important. Many articles suggest you should<br> use a "n x 64K" stripe size (like 64K, 128K, 256K).<br> <br> 2. After the RAIDn unit is available and viewable as "a disk" in Windows, you partition and format it. Now, what allocation unit should you choose? This is very a hard question to answer.<br> <br> So?<br> <br> Microsoft articles seem to suggest that it's always best to choose an 64K allocation unit.<br> That might be true. Surely, SQL server is their product, so they should know.<br> <br> Several other articles studied the performance (in general) using 64K and 128K stripe sets, in combination<br> with 4K, 8K and 64K allocation units. The results varies a lot, so IMHO, we cannot easily answer the question!<br> It all depends on storage, type of RAID, stripeset choosen, type of database etc..<br> In most cases, if we are talking about a very ordered Dataware House, with almost only reads and<br> very neat rebuild tables/indexes, I would also choose 64K allocation unit.<br> <br> So? I am afraid I was not able to fully answer the question of the "best allocation unit".<br> Hopefully you agree that's simply not easy to answer unless you know a lot of details.<br> But generally speaking, I would also say that an allocation unit of 64K <B>seem</B> right, since SQL server<br> for table and index data 'thinks' in 64K chunks (extents) anyway.<br> <br> <br> <B><U>Partition Alignment:</B></U><br> <br> Many articles which discuss "partition alignment", speak of a possible "overall" performance increase of about 15%-30%.<br> So, if you do this "right", it could have a significant effect.<br> <br> This work is or should typically be done by a storage admin, or sysadmin, who installs a Server and implements the disksubsystem.<br> <br> Maybe first you should check with those people, to get the facts (for your specific storage) straight.<br> In the unlikely case they answer with "huh..?", then <I>you</I> have to provide the neccessary input.<br> <br> Partition alignment, (or volume alignment, or sector alignment as it is called occasionally) has "something to do"<br> that the offset of the start of the partition, from the very beginning of the disk, should align with the stripset<br> that is in effect on your RAID system.<br> If this not the case, more IO is done than is strictly neccessary.<br> Now, the point is, the Vendor of your diskarry, or the person who installs it, should know these "magic" numbers.<br> In practice however, if the information is not available, there are generic setups that seem to work.<br> <br> If you setup partitions under Win2K8, you will not be "bothered" by the misaligned offset. This system will take care of it.<br> But, on Win2K3, it could be an issue.<br> <br> Anyway, if on Win2K3, a 64K offset (128 sectors) is a common value that works on many storage arrays.<br> Win2K8 uses a 1024K offset, which should work even better.<br> So, we will take that as our preffered offset.<br> <br> How to do it yourself? The tools you could use are "diskpar" or "diskpart".<br> However, DiskPart.exe is the preferred method since it's newer and is included as of Win2K3 sp1.<br> <font face="courier" size=2 color="black"> <br> DISKPART> list disk<br> <br> <I> shows a list of your disks... </I><br> <br> Now, suppose you want to align and then format disk 3: <br> DISKPART> select disk 3<br> Disk 3 is now the selected disk.<br> DISKPART> create partition primary align=1024<br> DiskPart succeeded in creating the specified partition.<br> DISKPART> assign letter=F<br> DiskPart successfully assigned the drive letter or mount point.<br> DISKPART> format fs=ntfs unit=64K label="SQLINDEX"<br> <br> Note that in the above example, I choose for align=1024, which is 1MB or 2048 sectors.<br> It's very likely that this boundary will match most stripe units.<br> <br> After the formatting is done, you can check the offset using the "wmic" command, like so:<br> <br> C:\> wmic partition get BlockSize, StartingOffset, Name, Index<br> <br> <font face="arial" size=2 color="black"> <br> There are so many more considerations on storage. For example, a feature like "HBA queue length" is important as well.<br> So, in general, I would say that DBA's just need storage specialists for large projects.<br> <br> But at least it's good to know that the right choices on "allocation unit" and "partition alignment" could play<br> "some" role as well in "overall" performance.<br> <br> <br> <h2>Chapter 7. Placement of objects on Filegroups.</h2><br> <h3>7.1 The "traditional" non-partitioning approach:</h3> If you are not too familiar with the concept of "filegroups", here is a small demo.<br> In the example below, we are going to create the database "SALES", and instead of <I> just having<br> only the PRIMARY filegroup (the default)</I>, we make two additional "logical containers": SALESDATA01 and SALESINDEX01.<br> <br> A filegroup can contain one or more files (usually more than one, if we have a large database).<br> The "trick" is, to let the files of a certain filegroup, reside on <B>another filesystem</B>,<br> than the files of the other filegroups.<br> <br> If those filesystems correspond to really different diskvolumes, we can achieve parallel IO.<br> This is so, because when you create a Table or an Index, you can specify (as a clause), on which<br> filegroup it is supposed to live.<br> Or, with existing tables and indexes, it's possible to move tables, and non-clustererd indexes, to their own filegroups.<br> <br> So, filegroups make it possible to seperate tables and indexes, and nothing prevents you from further<br> seperate large tables (or indexes) on their own filegroups (just create the appropriate number of filegroups).<br> <br> <font face="courier" size=2 color="black"> create database SALES<br> on PRIMARY<br> (<br> name='SALES',<br> filename='f:\mssql\dATA\SALES.mdf'<br>, size=400MB,<br> filegrowth= 0MB,<br> maxsize= 400MB<br> ),<br> FILEGROUP SALESDATA01<br> (<br> name='SALES_DATA_01',<br> filename='g:\mssql\data\SALES_DATA_01.ndf',<br> size= 4000MB,<br> filegrowth= 100MB,<br> maxsize= 8000MB<br> ),<br> FILEGROUP SALESINDEX01<br> (<br> name='SALES_INDEX_01',<br> filename='h:\mssql\data\SALES_INDEX_01.ndf',<br> size= 4000MB,<br> filegrowth= 100MB,<br> maxsize= 8000MB<br> )<br> LOG ON<br> (<br> name='SALES_LOG_001',<br> filename='i:\mssql\log\SALES_LOG_001.ldf',<br> size= 3000MB,<br> filegrowth= 100MB,<br> maxsize= 8000MB<br> )<br> <br> ALTER DATABASE SALES<br> MODIFY FILEGROUP SALESDATA01 DEFAULT<br> GO<br> <br> <font face="arial" size=2 color="black"> In the above example, you see that I only have F:, G:, and H: for filegroups for tables and indexes.<br> It's very important to put the "transactionlog file(s)" seperate from the above filesystems for tables and indexes.<br> In the above example, the transactionlog resides on I:<br> <br> <B>Approach 1:</B><br> <br> As you have seen from chapter 4, if a table has a clustered index, in effect, the leafpages of that clustered index<br> are the tablepages theselves.<br> So, if all (or most) of your tables have a Primary Key (and are enforced by the unique clustered index), then you could follow this approach:<br> <br> <B>Create nonclustered indexes on a filegroup other than the filegroup of the table (= clustered index).</B><br> <br> And repeat that approach for all other relevant large and/or active tables.<br> Ofcourse, you cannot give every non-clustered index it's own filegroup, so probably you will put a fairly large number<br> of indexes on "filegroupA", and possibly another fairly large number of indexes on "filegroupB".<br> <br> This is all actually no more than "common sense".<br> <br> You can even apply this approach for the largest and most active tables. Even if your database has hundreds, or even thousents<br> of tables, I am sure, that using the queries from chapter 4, you will discover that only 10, or 20 or maybe 30, really large<br> and/or active tables are present. Suppose you have found tables A,B,C,D,E,F,G,H to be very large.<br> Now, nothing prevents you from placing Tables A, B, C, D on filegroup "FG_ABCD", and place the tables E, F, G, H on filegroup "FG_EFGH".<br> <br> As a small bonus, having the non-clustered indexes on their seperate filegroup(s), you have some level of additional protection.<br> If the drive(s) containing those filegroup(s) go bad, or something else crashes, you can regenerated the non-clustered indexes again.<br> Only the tables (or the clustered indexes) contain the "true" data. All other indexes can be re-created, although it could<br> take quite some time.<br> <br> Note: always have a recent database script with create statements of all tables, indexes and all other objects !<br> <br> <br> <B>Approach 2:</B><br> <br> Approach number 1, is most appealing to me. But some DBA's do something else. They just mix tables and non-clustered indexes<br> among a number of filegroups on different diskdrives. That's not a bad idea either.<br> <br> It's evident, that here, you are most sure that all diskdrives are used at all times.<br> <br> You only need to create a list of the largest and/or most active tables and indexes, and distribute those object<br> on several filegroups (which themselves should reside on different "spindels").<br> <br> If you want to see which clustered and non_clustered indexes reside on which filegroups, you might use this query:<br> <br> <font face="courier" size=2 color="black"> SELECT O.Name AS [Object Name], O.[Type], I.name AS [Index name], I.Index_Id, I.type_desc AS [Index Type], F.name AS [Filegroup Name]<br> FROM sys.indexes I INNER JOIN sys.filegroups F<br> ON I.data_space_id = F.data_space_id<br> INNER JOIN sys.objects O ON I.[object_id] = O.[object_id]<br> GO<br> <br> If you do not want to 'see' the systemviews (starting their name often with 'sys') in the output,<br> then use:<br> <br> SELECT O.Name AS [Object Name], O.[Type], I.name AS [Index name], I.Index_Id, I.type_desc AS [Index Type], F.name AS [Filegroup Name]<br> FROM sys.indexes I INNER JOIN sys.filegroups F<br> ON I.data_space_id = F.data_space_id<br> INNER JOIN sys.objects O ON I.[object_id] = O.[object_id]<br> WHERE O.Name not like 'sys%'<br> GO<br> <br> <font face="arial" size=2 color="black"> <br> <h3>7.2 Partitioning large tables and indexes:</h3> <br> Here is some <I>extremely short, and lightweight,</I> information on <B> table and index partitioning.</B><br> <br> In this section, we go one step further, by "partitioning" <B>subsets of the same one table</B>, on their own filegroups.<br> <br> Now let that sink in: we will devide a table, into parts, and put those parts on different filegroups. Essentially,<br> that's partitioning !<br> <br> Ideally, you would have a certain column in your table, that lends itself easily to "get partioned". In other words,<br> that particular column would have values that are easily divided into subsets, like for example "years".<br> For example, you might have a table with some date column, and you could group records belonging to<br> to the years "2000", "2001, "2002" etc..<br> <br> But even if it doesn't go in such a "natural way", you can always force some form of subsetting. For example, you have<br> a nummeric column, and you just are going to distinquish the following subsets:<br> <br> 1 - 10000000<br> 10000001 - 20000000<br> 20000001 - 30000000<br> etc..<br> <br> Creating a partitioned Table, or Index, is a 3 step process.<br> <br> Don't forget that the whole idea behind partitioning a table is this: create Ranges of values, where the rows of the table will<br> fall into, and make sure that you can store those different record subsets (the different Ranges), onto separate Filegroups.<br> <br> - We start by defining a Partition Function. This is function that defines the boundaries, or Partition Ranges, that the subsets of rows will use.<br> - Secondly, we create a Partitioning Scheme, that defines the mappings of the Partition Ranges to FileGroups (individual storage structures)<br> - Thirdly, we create a Table, using the definitions above.<br> <br> That's all.<br> <br> So, a simple example will illustrate this.<br> <br> 1. Suppose we have a certain database, which uses the filegroups FG1, FG2, FG3 and FG4.<br> <br> Suppose we have a table PARTSAMPLE that we want to partition. It uses the columns ID (datatype INT) and NAME (varchar(20)).<br> The values that ID can take, are for example 1& 1000& 7000& 20000& 30000 etc..<br> <br> 2. Now let's define the "Partition Ranges", or boundaries, that the subsets of rows can take:<br> We do that by creating a Partition Function, whereas later we are going to "bind" it somehow to the table.<br> <br> <font face="courier" size=2 color="black"> CREATE PARTITION FUNCTION samplefunction (INT)<br> AS<br> RANGE LEFT FOR VALUES (10000, 20000, 30000)<br> GO<br> <font face="arial" size=2 color="black"> <br> This function, is an independent object in the database. Later we will use it in the PARTSAMPLE table definition.<br> <br> The "LEFT" or "RIGHT" in the function definition means if you want the interval to "leftsided" or "rightsided" as in:<br> 10001 - 20000<br> 10000 - 19999<br> <br> 3. Next, we will define the "Partition Scheme", that will relate the Ranges to the Filegroups:<br> <br> <font face="courier" size=2 color="black"> CREATE PARTITON SCHEME samplescheme <br> AS<br> PARTITION samplefunction TO<br> ([FG1], [FG2], [FG3], [FG4])<br> GO<br> <font face="arial" size=2 color="black"> <br> You see that in the Partition Scheme definition, we relate the FileGroups to the "samplefunction" function, thus thereby<br> relating the FileGroups to the Ranges (or boundaries).<br> <br> 4. As the last step, we will define our PARTSAMPLE table, using the Partition Scheme defined above.<br> <br> <font face="courier" size=2 color="black"> CREATE TABLE PARTSAMPLE<br> (<br> ID INT NOT NULL,<br> NAME VARCHAR(20) NOT NULL<br> )<br> ON samplescheme(ID)<br> GO<br> <br> <font face="arial" size=2 color="black"> So, if a record with an ID of 15000 would be inserted, it would be stored on FileGroup "FG2".<br> Likewise, if a record with an ID of 25000 would be inserted, it would be stored on FileGroup "FG3".<br> <br> <br> <h2>Chapter 8. Other remarks on several subjects.</h2><br> In this chapter, we will review a couple of other important subjects.<br> <br> <h3>8.1 The right choice of datatypes.</h3> SQL Server knows a huge number of datatypes. If you define a table, you tell SQL Server the columnnames,<br> and the datatypes (and possibly other attributes as well).<br> For example, we have the datatypes "int" (integer), "decimal(n,m)", "char(n)", "varchar(n)", "datetime" etc..<br> <br> If you create the tables yourself for some application, or you are able to advise a developer, the following is<br> quite important.<br> <br> If you choose "too wide" datatypes, you fill a database block "too quickly". This means that a smaller number of rows<br> fits in a table page.<br> This cost performance, because the database needs to read more pages, to get the same imformation.<br> For example, suppose some table has a "COMMENT" column. Think of the consequence if some developer choose a<br> a datatype of "char(2000)", meaning a fixed column lenght of 2000 bytes.<br> Now if the comment is at most just a few words, then that's a true waste.<br> <br> So, always try to choose a datatype (with a "width") that suits the purpose, but also not <I>"that thight"</I>.<br> Just try to balance it a bit.<br> <br> The choice a datatypes also infuences the amount of "processor time".<br> If you think about a very active database, the first thing that comes to mind, is heavy Disk IO.<br> But certainly not in all cases. High cpu utilization is a very important performance issue too.<br> Now, dont think that this will only happen at the physics department, where difficult calculations are used.<br> <br> Don't underestimate financial applications, where quite elaborate calculations may be done on all sorts of assets<br> and securities etc..<br> Here too, the choice of the most optimal datatypes is important. It's difficult to say in general which dataypes<br> are the best. Just depends on the application. Hopefully, the developers are aware of this fact. Not all are, so I have noticed.<br> <br> <h3>8.2 Some remarks on TEMPDB.</h3> TEMPDB is a sort of "scratch workplace" for SQL Server. You might find many temporay tables here, which might be<br> used for all kinds of sorts, or as a sort of intermediate storage containers, during processing.<br> <br> A bottleneck in tempdb IO, can impact the overall throughput of your SQL Server.<br> <br> How active TEMPDB will be, depends on SQL Server itself, <B><U>and</U></B> the application.<br> Usually, the application is the most important factor. But, if for example, SQL Server "versioning" is in use,<br> then usage on TEMPDB can be very high.<br> <br> Fortunately, Windows System Monitor (called Performance Monitor in the past) has the neccessary counters<br> to make a judgement on how active TEMPD actually is.<br> <br> You can also write your own system queries that gives info on the activity on TEMPDB.<br> <br> Here are a few examples:<br> <br> The following will give an idea on allocated pages per session:<br> <font face="courier" size=2 color="black"> <br> USE TEMPDB<br> <br> SELECT top 10 * <br> FROM sys.dm_db_session_space_usage <br> ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC<br> <br> <font face="arial" size=2 color="black"> The following will give an idea on the number of temporary tables (which name starts with "#")<br> <br> <font face="courier" size=2 color="black"> USE TEMPDB<br> <br> select name, create_date, modify_date from sys.all_objects<br> where name like '%#%'<br> <br> <font face="arial" size=2 color="black"> A keypoint is this: if TEMPDB is very active, here too you might coinsider to place the TEMPDB data- and log files<br> on a fast disk which was not much used before.<br> Although many of us don't have that luxury, you might consider to place the files at for example a backup dump<br> disk, which might only be used "occasionally" (once a day, or once an hour, for full- or transaction backups).<br> Ofcourse, in some cases, that's not a good idea. Only you can determine how matters are in your situation.<br> <br> A recommendation you can find in many articles, is to let TEMPDB consist of several datafiles.<br> Some even recommend, that you let the number of files to be equal to the number of cpu cores.<br> <br> Here is a sample on how you could modify the TEMPDB database:<br> <br> <font face="courier" size=2 color="black"> ALTER DATABASE TEMPDB<br> MODIFY FILE<br> (NAME = tempdev,<br> SIZE = 10MB, -- keep this one small<br> MAXSIZE=10MB);<br> GO<br> <br> ALTER DATABASE TEMPDB ADD FILE ( -- let's add 4 datafiles<br> NAME = TempDevA,<br> FILENAME = 'M:\mssql\data\tempdeva.ndf',<br> SIZE = 100MB,<br> MAXSIZE = 200MB,<br> FILEGROWTH = 20MB),<br> (<br> NAME = TempDevB,<br> FILENAME = 'M:\mssql\data\tempdevb.ndf',<br> SIZE = 100MB,<br> MAXSIZE = 200MB,<br> FILEGROWTH = 20MB),<br> (<br> NAME = TempDevC,<br> FILENAME = 'M:\mssql\data\tempdevc.ndf',<br> SIZE = 100MB,<br> MAXSIZE = 200MB, FILEGROWTH = 20MB),<br> (<br> NAME = TempDevD,<br> FILENAME = 'M:\mssql\data\tempdevd.ndf',<br> SIZE = 100MB,<br> MAXSIZE = 200MB,<br> FILEGROWTH = 20MB)<br> GO <br> <font face="arial" size=2 color="black"> <br> <h3>8.3 Carefully consider the Max memory limit for SQL Server 64bit.</h3> Suppose you use SQL Server 2005/2008 64. What should be the maximum memory you should assign to SQL Server?<br> Actually, this <I>looks</I> easy, but it isn't.<br> <br> Suppose your x64 system has only 16GB of memory. Now, NT (meaning Win2k3, Win2k8 etc..) and all sysadmin programs<br> (like backup software, anti-virus, monitoring agents etc..) needs memory too, which can be considerable.<br> Because... you don't want that the system exhibits <I>excessive paging</I>.<br> There is no good alternative other than to discuss this with your nearest senior sysadmin.<br> So, in the upper example of a system of only 16GB, I suggest you limit it to 12GB.<br> <br> <B>Just a few words on NT paging.</B><br> <br> Using Performance Monitor counters to "measure" the amount of paging, can sometimes be a bit confusing.<br> If you see a moderate or high value of "Memory: Pages/sec", it does not neccesarily need all to be caused by paging alone,<br> or pages from and to cache.<br> Some applications or tools might "page" to memory mapped files, which will "distort" the view considerably.<br> Besides that, many causes exist why "Memory: Pages/sec" produce high values. But anyway, it should not be <I>too high</I> ofcourse.<br> <br> You might have high amount of paging, if you see:<br> "Memory: Pages/sec" - moderate to high.<br> "Memory: Available Bytes" - would be low.<br> "Paging File: % Usage" - would be high.<br> "Paging File: % Peak Usage" - would be high.<br> "Memory: Pages Output/Sec" - would be high.<br> <br> This last counter shows how many virtual memory pages were written to the pagefile to free RAM page frames,<br> and is therefore a good indicator.<br> <br> It's true that NT will always page to a certain extent.<br> Especially, with all monitoring "tools" loaded, NT systems will certainly page somewhat.<br> <br> As to the size of the pagefile, as a rule of thumb for "low" memory systems, it should be around 1.5xTotal RAM.<br> Ofcourse, the more memory you have, the less important the size of the pagefile is.<br> No sysadmin will ever create a pagefile of 128GB, if the system has 64GB RAM.<br> <br> <h3>8.4 Commit limits and remote logging of Batches.</h3> What I have seen occasionally, are two sort of "misconfigurations" that can affect performance in a negative way.<br> <br> Most applications, will have some sort of batch facility, which is likely to be scheduled after working hours.<br> The architecture of such batches, is very diverse.<br> <br> What can be worthwile, is to investigate if some "component" does "logging" (to a logfile) at a <B>remote</B> host.<br> If in such a case, a sort of "SEND - Acknowledge" scheme is in use, that can slow down the batch significantly.<br> <br> Also, it can be worthwile if the "metadata" (in configfiles, or in the registry etc..) of the application, uses<br> some sort of "commit limit". What I mean is this. Such an application generates smaller or larger batches of transactions.<br> Each of these batches is comitted. But what is the lenght? Is such a batch 1, 10, 1000, 10000 records "wide"? In the extreme, if a "batch" is only 1 "wide", that too can slow down the batch significantly.<br> <br> <br> <h2>Chapter 9. Some real world cases, describing somewhat more complex performance problems.</h2><br> <h3>9.1 Some important (not so trivial) wait_types:</h3> In this note, we have seen a few subjects that might be the origin for performance problems.<br> As said right from the start of this note, we do not cover <I>Query Design</I>.<br> Actually, this is one of the most common causes of low performance. Indeed, the impact can be quite severe.<br> <br> Now suppose you have, what seems to be, a well configured system. So, you have sufficient memory, and Disk IO seems to be fine,<br> lots of cpu power etc.. etc..<br> <br> Still you might encounter what appears to be "performance issues".<br> <br> Here are a few important wait_types to watch for:<br> <br> <B>CXPACKET:</B> Probably related to "Parallelism".<br> <B>SOS_SCHEDULER_YIELD:</B> Might be an indicator of CPU pressure.<br> <B>ASYNC_NETWORK_IO:</B> Might be an indicator of poor Network I/O.<br> <B>LCK_X, LCK_M_U, & LCK_M_X:</B> Long lasting locks, and/or locks that involve many extents (leading to blocking).<br> <B>PAGEIOLATCH_X:</B> Buffer I/O latch, maybe due to poor Disk IO, or low on memory.<br> <B>ASYNC_IO_COMPLETION & IO_COMPLETION:</B> Could also be attributed to poor Disk IO, or general IO issues.<br> <B>PAGELATCH_X:</B> Buffer latch problems.<br> <B>WRITELOG & LOGBUFFER:</B> Might be due to poor IO to the Tranaction log disk subsystem.<br> <br> In the next sections we will some examples of these wait_types.<br> <h3>9.2 LCK_M_U, CXPACKET, SOS_SCHEDULER_YIELD, DTC</h3> <B>Problem: you see high "wait times" associated with one or more of the "wait_types" LCK_M_U, CXPACKET, SOS_SCHEDULER_YIELD,<br> and possibly also DTC. Secondly, you <I>might</I> (but not neccessarily so) find a lot of dealocks in some application log, and/or users complains<br> that their frontend applications often "stalls".</B><br> <br> We know that "under the hood" systems like SQL Server, are incredably complex. But, SQL Server 2005/2008 generates<br> a lot of tracing or logging "material", which you can "see" using the "dynamic mamagement views (DMV's)".<br> Although you may not call it "tracing material", the DMV's sure functions that way.<br> <br> The "keywords" in the title of this section, look pretty impressive. Actually, these are "wait_types".<br> If you switch back quicly to section 1.3, you find a nice query giving you a top 10 "wait times"<br> and telling you what "wait types" they are associated with.<br> Note from the figure in section 1.3, that the "waittimes" due to "wait_type=CXPACKET" are pretty high too.<br> The wait_types CXPACKET, SOS_SCHEDULER_YIELD, LCK_M_U are in many cases, related to each other.<br> <br> <B><U>CXPACKET</U></B><br> <br> CXPACKET waits may indicate parallelism problems. This basically means you are running a parallel process and one<br> or more threads of it, are waiting for others to complete. CXPackets occur when a query has its operations run in parallel,<br> but not all operations complete at the same time. SQL Server cannot continue to the next SQL statement <br> because not all operations have completed. This results in waiting defined as CXPacket.<br> The setting that determines if "parallelism" is "switched on", is "Maximum Degree of Parallelism" or MAXDOP.<br> If you rightclick the server object in SQL Server Management Studio, you can view and modify all sorts of settings.<br> In the "Advanced" page, you will find the "Maximum Degree of Parallelism" setting. If you put it to "1"<br> you have it switched "off", although TSQL statements can override it with the MAXDOP clause.<br> Ofcourse, different sessions all operate, and stay to operate, in "parallel", but the parallelism of one statement is off.</li><br> If the MAXDOP setting was, for example, "8", you might lower the value, and see what the result is.<br> Unfortunately, tackling CXPACKET waits usually means a lot of testing (trial and error)<br> <br> <B><U>LCK_M_U</U></B><br> <br> Actually, there are many "LCK_M*" wait-types, like:<br> <br> Lck_M_U is associated to update locks, that might cause other processes to wait, and<br> Lck_M_X is associated to exclusive locks, which too might cause other processes to wait.<br> <br> The LCK_M_U wait_type might be found in many cases. It occurs when a task is waiting to acquire an Update lock, which it can't<br> do at that time, because another process already has aquired a lock of some kind.<br> <br> <B>-> Possible causes and some guidelines to what to do:</B><br> <br> A possible cause to high values of CXPACKET and LCK_M_U might be threads of an application that are frequently waiting on each other.<br> In this case, it might be due to ineffective "application design".<br> <br> If you also see considerable "DTC" waits. them that might be a clue that distributed transactions are not<br> effectively doing their work.<br> In this case too, it might be due to ineffective "application design".<br> Knowing the internals of MSDTC and distributed transactions and application design, is a complex world of it's own.<br> <br> If you have a system with many cpu's, it could even be counterproductive at some batches or statements which are allowed<br> to use parallelism. It might thus help to reduce the MAXDOP setting, as explained above.<br> Ofcourse, having many cpu's is very good for overall throughput, and independent sessions are then running<br> in parallel.<br> But, with some particular queries, they might use parallelism that might give rise to LCK_M* and CXPACKET waittimes.<br> <br> Sometimes, using the SNAPSHOT Isolation Level with the option "ALLOW_SNAPSHOT_ISOLATION" set to ON at the database level,<br> might improve performance.<br> <br> <h3>9.3 Isolation Levels.</h3> <br> <h2>Chapter 10: A few good links to technical articless:</h2> <br> -> If you want more depth on SQL Server performance discussions, here is a good Microsoft article:<br> <br> <a href="http://technet.microsoft.com/en-us/library/cc966540.aspx">Microsoft Technet article</a><br> <br> -> Great link with good queries:<br> <br> <a href="http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2061.entry">glennberrysqlperformance.spaces.live.com</a><br> <br> -> If you want more depth on SQL Server disk storage design considerations, here is a good link:<br> <br> <a href="http://technet.microsoft.com/en-us/library/cc966414.aspx">Microsoft Technet article</a><br> <br> -> If you want more depth on the specifics on TEMPDB, here is a good link:<br> <br> <a href="http://technet.microsoft.com/en-us/library/cc966545.aspx">Microsoft Technet article</a><br> <br> -> Some general performance papers:<br> <br> <a href="http://sqlcat.com/top10lists/archive/2007/11/21/top-10-sql-server-2005-performance-issues-for-data-warehouse-and-reporting-applications.aspx">Top 10 SQL Server performance issues in DW</a><br> <a href="http://sqlcat.com/top10lists/archive/2007/11/21/top-sql-server-2005-performance-issues-for-oltp-applications.aspx">Top 10 SQL Server performance issues in OLTP</a><br> <br> Note: There are some other docs from myself too (excel files):<br> <br> <a href="http://antapex.org/sql2005_keypoints.xls">Listing of some SQL Server 2005 facts & structures (for exam 070-431, 5.5 MB)</a><br> <a href="http://antapex.org/sql2008_keypoints.xls">Listing of some SQL Server 2008 facts & structures (for exam 070-432, 9 MB)</a><br> <br> <br> That's it. Hope it was of any help. <br> <br> <br> <br> </body> </html>