|
|
Alla Taggar
All Tags » Performance (RSS)
-
Ola has a set of stored procedures to do maint operations, see http://ola.hallengren.com/ . Ola has now updated them to support exclusions or inclusions of tables, indexes or even whole schemas from index rebuild/reorg. Check out http://ola.hallengren.com/Documentation.html#SelectingIndexes...( read more) 
|
-
You know the answer already: It depends. But I often see some percentage value quoted and the point of this post is to show that there is no such percentage value. To get the most out of this blog post, you should understand the basic structure for an...( read more) 
|
-
I just read in a forum about a user who want to replikate a table, but the table doesn't have a PK. The table is pretty large, and having the table not available while adding the PK is undesireable. The table has a clustered index already, and there are...( read more) 
|
-
I recently read a discussion whether RPC events add overhead compared to just submitting text. I got curious and did some testing, which I want to share. Background, SQL events Using most APIs, if you just submit a query to SQL Server, you will get what...( read more) 
|
-
Let's start with some background on forwarding pointers: Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new...( read more) 
|
-
I see a trend towards more and more focusing on response time; and less and less on resource usage (resource consumption). I've even seen blanket statements such as the only thing that matters is response time. I do not agree. I feel that by being a good citizen and consume as few resources and possible, we contribute to the overall welfare of the system.
For instance, I'm fortunate to have some 8 km (5 miles) to my main client. I can take the car, which often is about 15 minutes or I can bicycle, which is about 20 minutes. For many reasons, I prefer to bicycle. The thing here is that I compromise a little bit and accept a few more minutes when going by bicycle, but I feel I'm a better citizen and contribute to a more sustainable society. But not only that: ever so often, the traffic is congested, and now the car takes some 40-45 minutes (bicycle still 20 minutes). By using the bicycle I both consume less resources and I also have a higher degree of predictability. Now, is this analogy appropriate to database performance? I don't know, perhaps to some extent... But let me give you a database example, from real life, followed by a TSQL example:
I have a client who had this query which used to be quick (enough) and suddenly was very slow. Been there before, we know this can be just about anything. Anyhow, it was pretty quick for me to find the reason. The query had an ORDER BY and a FAST hint. The FAST hint tells SQL Server to return rows to the client as fast as possible, but possibly with a higher overall cost. The developer who added that FAST hint didn't really think that hard about it, and just "threw it in there". It sounds good, doesn't it? There was a non-clustered index (non-covering) on the sort column and also some restrictions (WHERE clause).
With the FAST hint, SQL Server used the index on the sort column to drive the query and for each row it did a "bookmark lookup" to fetch the row. This means a page access for each row, but rows can be returned to the client application immediately (think streaming). Without the fast hint, SQL Server first sorted the relevant rows into a worktable and then returned the rows in sorted order from that worktable. So we have a tradeoff between reading a lot of pages (possibly some from cache) or doing some work up-front to sort data and then just read that sorted worktable sequentially.
The worrying part here is that with a small table, it will fit in cache and the difference between the two might not be that drastic (either way). But as table grew larger, it won't fit in cache anymore and as we see logical I/O turning into physical I/O things really go south for the query with the FAST hint. This is what happened to my client. Table grew and a query which had OK response time suddenly was a disaster. If that FAST hint wasn't there in the first place, my client wouldn't have this slowness in the application over the two weeks it took until I had time to look over it and remove the FAST hint (I also added a couple of indexes, but that is beside the point).
Seeing is believing, right? At the end of this blog post, you will find TSQL that pretty much mimics my client's case. It populates a table with 800,000 rows and there's a non-clustered index on the sort column. We then try some variations to check response time, CPU seconds usage, I/O and duration. I measured response time using TSQL (as seen in the script). I also measured response time and the other metrics using Profiler.
The size of the table (clustered index on identity column) is 133MB and the non-clustered index to on the sort column is 11MB. This is a small table, but that makes things more manageable; and by setting the max server memory to a low value (60MB), we can still see the effect of logical vs. physical I/O.
We first run the query and have a filter that restricts to 4,000 rows out of 800,000 rows. Note that there's no index on the filter column.
- The query without a FAST hint was very consistent. The response time was either 0.05 seconds (without clearing cache first) or 1.9 seconds (if we clear cache first). This was regardless of if we configured with 500MB or 50MB memory for sp_configure 'max server memory'.
- The query with FAST hint was OK with memory setting of 500MB, so the table would fit in cache: 1.6 seconds to 4.4 seconds (depending on whether we empty cache before execution). But when we lower memory setting (think "large table"), the execution time jumped up to 73 seconds. That is a factor of between 48 and 1460.
Things got a bit different when we removed the WHERE clause to return all rows:
- Query without FAST hint took between 10 seconds and 23 seconds (depending on whether we empty cache first) for a memory setting of 500MB. Lowering memory to 60MB made this one take between 23 and 41 seconds. Note that I here got some error messages from SQL Server regarding insufficient memory in the internal memory pool (possibly SQL Server now did some fall-back strategy for the query execution, which added to execution time).
- The query with the FAST hint outperformed the one without for a memory setting of 500MB, with execution time between 2.2 and 5.6 seconds. Note that I configured SSMS to discard results so there is no processing of the returned 800,000 rows included here. With a memory setting of 60MB, we again bumped up execution time to some 74 seconds.
Here are the full numbers:
| ms |
ms Profiler |
ms cpu |
io |
fast hint |
memconf |
cache clean |
rows returned |
| 1930 |
2023 |
202 |
18782 |
0 |
500 |
1 |
4000 |
| 53 |
60 |
110 |
18768 |
0 |
500 |
0 |
4000 |
| 4403 |
4497 |
2075 |
2695310 |
1 |
500 |
1 |
4000 |
| 1616 |
1622 |
1622 |
2551439 |
1 |
500 |
0 |
4000 |
| 1930 |
1977 |
171 |
18768 |
0 |
60 |
1 |
4000 |
| 56 |
59 |
94 |
18768 |
0 |
60 |
0 |
4000 |
| 72426 |
72479 |
10888 |
5513944 |
1 |
60 |
1 |
4000 |
| 72663 |
72728 |
10983 |
5521626 |
1 |
60 |
0 |
4000 |
| 23336 |
23391 |
2105 |
31738 |
0 |
500 |
1 |
800000 |
| 10263 |
10269 |
2559 |
31574 |
0 |
500 |
0 |
800000 |
| 5663 |
5703 |
2386 |
2695368 |
1 |
500 |
1 |
800000 |
| 2226 |
2235 |
2028 |
2551439 |
1 |
500 |
0 |
800000 |
| 40966 |
40975 |
2620 |
31654 |
0 |
60 |
1 |
800000 |
| 22906 |
22913 |
2714 |
31629 |
0 |
60 |
0 |
800000 |
| 73676 |
73709 |
11045 |
5512080 |
1 |
60 |
1 |
800000 |
| 74513 |
74557 |
11778 |
5522556 |
1 |
60 |
0 |
800000 |
For the sake of completeness, I should add that having a good supporting index for the restriction (for the queries that had a restriction) made the query go equally fast regardless of memory config or FAST hint (in fact the FAST hint was irrelevant with a good index).
Here's the T-SQL if you want to play with it. As always, don't execute anything if you don't understand the code and the consequences of executing it! EXEC sp_configure 'max server memory', 500
RECONFIGURE
GO
USE master
GO
IF DB_ID('TestDb') IS NOT NULL DROP DATABASE TestDb
GO
CREATE DATABASE [TestDb]
ON
PRIMARY
(NAME = N'TDb', FILENAME = N'C:\TDb.mdf'
,SIZE= 100MB, MAXSIZE = 200MB, FILEGROWTH = 30MB )
LOG ON
(NAME = N'TDb_l', FILENAME = N'C:\TDb_l.ldf'
,SIZE = 200MB, MAXSIZE = 500MB, FILEGROWTH = 20MB )
GO
USE testDb
CREATE TABLE t(c1 INT IDENTITY PRIMARY KEY CLUSTERED, c2 INT, c3 INT, filler CHAR(150))
INSERT INTO t (c2, c3, filler)
SELECT TOP(800000) 1, 1, 'hi'
FROM sys.columns AS a
CROSS JOIN sys.columns AS b
CROSS JOIN sys.columns AS c
UPDATE t SET c2 = c1 % 20, c3 = c1 % 200
CREATE NONCLUSTERED INDEX x ON t(c2)
--Size of table and indexes
EXEC sp_indexinfo t
--Can be found at http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
IF OBJECT_ID('tmp') IS NOT NULL DROP TABLE tmp
GO
CREATE TABLE tmp
(seq tinyint IDENTITY(1,1) PRIMARY KEY NOT NULL
,ms INT NOT NULL
,ms_profiler INT NULL
,ms_cpu INT NULL
,io_ INT NULL
,fast_hint bit NOT NULL
,memconf smallint NOT NULL
,cache_clean bit NOT NULL
,rows_returned INT NOT NULL)
GO
-----------------------------------------------------------------------------------------
--Create procedures
IF OBJECT_ID('emptyCache') IS NOT NULL DROP PROC emptyCache
GO
CREATE PROC emptyCache AS
BEGIN
CHECKPOINT
DBCC DROPCLEANBUFFERS
END
GO
IF OBJECT_ID('do_it') IS NOT NULL DROP PROC do_it
GO
CREATE PROC do_it
@fast_hint bit, @memconf smallint, @cacheclean bit, @rows_returned INT
WITH RECOMPILE
AS
BEGIN
DECLARE @dt datetime SET @dt = GETDATE()
IF @fast_hint = CAST(0 AS bit)
IF @rows_returned = 4000
SELECT * FROM t WHERE c3 = 16 ORDER BY c2
ELSE --return all rows
SELECT * FROM t ORDER BY c2
ELSE --add FAST hint
IF @rows_returned = 4000
SELECT * FROM t WHERE c3 = 16 ORDER BY c2 OPTION(FAST 20)
ELSE --return all rows
SELECT * FROM t ORDER BY c2 OPTION(FAST 20)
INSERT INTO tmp(ms, fast_hint, memconf, cache_clean, rows_returned)
VALUES(DATEDIFF(ms, @dt, GETDATE()), @fast_hint, @memconf, @cacheclean, @rows_returned)
END
GO
TRUNCATE TABLE tmp
-----------------------------------------------------------------------------------------
--Return 4000 rows
-----------------------------------------------------------------------------------------
--500 MB memory
EXEC sp_configure 'max server memory', 500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 0, @memconf = 500, @cacheclean = 1, @rows_returned = 4000
GO
EXEC do_it @fast_hint = 0, @memconf = 500, @cacheclean = 0, @rows_returned = 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 1, @memconf = 500, @cacheclean = 1, @rows_returned = 4000
GO
EXEC do_it @fast_hint = 1, @memconf = 500, @cacheclean = 0, @rows_returned = 4000
GO
--50 MB memory
EXEC sp_configure 'max server memory', 60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 0, @memconf = 60, @cacheclean = 1, @rows_returned = 4000
GO
EXEC do_it @fast_hint = 0, @memconf = 60, @cacheclean = 0, @rows_returned = 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 1, @memconf = 60, @cacheclean = 1, @rows_returned = 4000
GO
EXEC do_it @fast_hint = 1, @memconf = 60, @cacheclean = 0, @rows_returned = 4000
GO
------------------------------------------------------------------------------------
--Return all 800,000 rows
------------------------------------------------------------------------------------
--500 MB memory
EXEC sp_configure 'max server memory', 500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 0, @memconf = 500, @cacheclean = 1, @rows_returned = 800000
GO
EXEC do_it @fast_hint = 0, @memconf = 500, @cacheclean = 0, @rows_returned = 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 1, @memconf = 500, @cacheclean = 1, @rows_returneed = 800000
GO
EXEC do_it @fast_hint = 1, @memconf = 500, @cacheclean = 0, @rows_returned = 800000
GO
--50 MB memory
EXEC sp_configure 'max server memory', 60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 0, @memconf = 60, @cacheclean = 1, @rows_returned = 800000
GO
EXEC do_it @fast_hint = 0, @memconf = 60, @cacheclean = 0, @rows_returned = 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint = 1, @memconf = 60, @cacheclean = 1, @rows_returned = 800000
GO
EXEC do_it @fast_hint = 1, @memconf = 60, @cacheclean = 0, @rows_returned = 800000
GO
 
|
-
This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the column side - not the literal side of your query. What does that mean? Consider below:
WHERE Col = Val
Now, say that the types for above don't match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren't of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for "Data Type Precedence".
What we don't want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here's the repro script from that thread (slightly adjusted by me):
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t
(
c1 int IDENTITY(10000001,1) NOT NULL
,c2 char(8) NULL
,c3 datetime NULL
)
GO
INSERT INTO t(c3)
SELECT TOP 3000000 '20080203'
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
UPDATE t SET c2 = CAST(c1 AS char(8))
CREATE UNIQUE CLUSTERED INDEX x ON t(c2)
GO
SELECT * FROM t WHERE c2 = N'10000009'
SELECT * FROM t WHERE c2 = N'10000008'
Don't bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.
Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find "WHERE:(Convert([t].[c2])=[@1])". See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That's just because we were a bit sloppy... Compare to below: SELECT * FROM t WHERE c2 = '10000009'
SELECT * FROM t WHERE c2 = '10000008'
You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.
OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn't see any difference between the two original queries (N'10000009' and N'10000008'). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the "9" query was slower than the "8" query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the "8" version vs. the "9" version. I used Profiler and confirmed. The "9" version had consistently about 90000 microsecond duration where the "8" version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn't give me anything. Perhaps I didn't capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. :-)
My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N'10000009' compared to N'10000008'. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like "Constant Scan" and "GetRangeThroughConvert" for other blog post on this topic.)
The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!
|
-
You might ask yourself: What is it and why does it matter?
What it is:
It allow SQL Server to allocate storage (space from disks) very very quickly. As you probably know, when you delete files they are not physically deleted from the disk - they are only marked as deleted in the file system allocation structures. This is why various undelete programs can recover deleted files. So imagine a process requiring disk space. The disk area given to the process from the file system and operating system could potentially contains some secret information from deleted files. This is why the file system/OS insist to zero out the storage before the process can actually see it. (I'm not OS / file system expert so anyone is free to correct me here.) That is, unless the one allocating the storage has some special privileges.
When does it occur?
Whenever SQL Server need disk space, such as:
- Create database
- Add file to database
- Restore (if the restore process includes database creation)
- File growth (both manual and auto-grow)
- Backup
Can we avoid the slow zeroing out space?
Yes, but only if you are on SQL Seerver 2005 or higher and for some operations: creation and allocation of data database files (mdf and ndf). The SQL Server service account need to have appropriate permissions from the OS. To be more precise, it need to have a privilege called SE_MANAGE_VOLUME_NAME. This is by default granted to Administrators. Do you run your SQL Server as an account being member of Administrators? I hope not. Did you grant this permission to the service account?
How do I grant this privilege?
This is easy. Add the SQL Server service account to the "Perform Volume Maintenance Tasks" security policy.
Does it matter?
You be the judge. Just to give you an idea, I created a database with a data file of various size (I had the log file at 1MB for all tests in order for it to influence the least). I timed it both with and without Instant File Initialization. I ran it on my desktop machine which has a RAID0 of two 7200RPM disks:
| Size |
without IFI |
with IFI
|
| 1GB |
10.3 s |
0.3 s |
| 10GB |
128 s |
1.3 s |
| 50GB |
663 s |
4.5 s |
The difference is roughly a factor of 100!
When does it hurt?
Basically every time disk storage is allocated. But let us focus of the occasions where you can do anything about it, i.e., when you can have Instant File Initialization. Such occasions include:
- Whenever a database is created. Space need to be allocated for the data file(s).
- Whenever a data file is manually expanded in size.
- Whenever you have auto-grow for a data file. Note that potentially some poor user will now sit and wait for the auto-grow to complete.
- When you start SQL Server. Why? Tempdb need to be re-created.
- When you perform restore, if the destination database not already exists with matching database file structure.
How can I tell if I have Instant File Initialization?
I find it easiest to just create a database and time it, using some reasonable size for your data file, like 5GB. Actually, run two test: One with 5GB data file and really small log file. And then another with very small data file and 5GB log file. Remember that we never have Instant File Initialization for log files. For instance, run below and you will see (adjust the file path for the database files). You need to adapt your code for file path name, possibly database name and the datetime handling if you are lower then SQL Server 2008: DECLARE @t time(3) = SYSDATETIME()
CREATE DATABASE IFI_test_ld
ON PRIMARY
(NAME = N'IFI_test', FILENAME = N'C:\IFI_test\IFI_test_ld.mdf', SIZE = 5GB, FILEGROWTH = 100MB)
LOG ON
(NAME = N'IFI_test_log', FILENAME = N'C:\IFI_test\IFI_test_ld.ldf', SIZE = 1MB, FILEGROWTH = 10MB)
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) AS LargeDataFile
SET @t = SYSDATETIME()
CREATE DATABASE IFI_test_ll
ON PRIMARY
(NAME = N'IFI_test', FILENAME = N'C:\IFI_test\IFI_test_ll.mdf', SIZE = 3MB, FILEGROWTH = 100MB)
LOG ON
(NAME = N'IFI_test_log', FILENAME = N'C:\IFI_test\IFI_test_ll.ldf', SIZE = 5GB, FILEGROWTH = 10MB)
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) AS LargeLogFile
Are numbers for above two about the same? If yes, then you don't have Instant File Initialization. If the one with large data file is much quicker, then you do have Instant File Initialization. And now you also know approx how long it takes to allocate 1 GB with of data and log file for your SQL Server.
I'm curious: Did you have Instant File Initialization?
|
-
For a long while, I've had a few things I wanted to add for sp_indexinfo (my procedure which returns bunch of information for indexes).
Dejan Sarka suggested adding XML indexes as well as making the column list look nicer. I've also had a request to add some documentation of what the procedure returns. Done. :-)
http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
|
-
Two weeks ago, we did the "SQL 2008 Summit" roadshow here in Sweden. We did 4 cities around Sweden in 4 days (http://www.expertzone.se/sql2k8/). It was a bit exhaustive, but even more fun - being able to travel and spend some time with persons wish I could meet more often (Kalen), others I meet regularly but only at workplace (Roger, Patrik, Anna) and yet other persons I just met (George, Per).
One of my presentations was on Resource Governor (RG), and I has this super-simple demo meaning to show CPU throttling. I classified connections to one of two Workload Groups based on login name. One group used a Resource Pool with max CPU at 10% and the other a Resource Pool with max CPU at 90%. Since I have two CPU cores, I started two execution loops for each login. An execution loop uses SQLCMD to login using the appropriate loginID and execute a proc which loops and outputs a counter using RAISERROR and NOWAIT (so we see something happening in the console).
For two of my presentations it worked just as expected. For two presentations it didn't: the CPU usage looked very very strange - nowhere near what we expected. So, during the final day, I managed to spend some time with Mikael Colliander from MS Sweden. First we couldn't reproduce the strange behavior, but after several restart, re-boot etc. we could. We now finally got to look at what scheduler each connection was using and there was the answer. One connection (ending up in the pool with max 10% CPU) was alone on one scheduler meaning alone on one CPU! The other three connections (one one on 10% CPU and two on max 90% CPU) was using the other CPU. So for the CPU where we had only one connection (belonging to the pool to be capped at 10% CPU) we just had no contention. So this connection could use everything on that CPU since nobody else was assigned to the CPU.
Now when I understand why this happened, it doesn't look that strange. But I think we need to be very careful when we monitor resource usage for our connections and are using resource governor. The more CPUs we have the less chance we will see the (perhaps expected) distribution of load.
|
-
Let me start by saying that the contents of this post is not very advanced. If you have read the excellent paper "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005", http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx and understood it, you would already know below, and much more...
I was reading a thread in the open newsgroups today (.tools, posted by Mike), about an advice that the SQL Server 2005 Database Engine Tuning Advisor apparently gave. It advices to replace sp_executesql usage with declaring TSQL variables and using those in the WHERE clause. Translated to the Adventureworks database, it advices that instead of 1) below, we should use 2) below.
1)
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title
FROM Person.Person
WHERE LastName = @P1',
N'@P1 nvarchar(50)', 'Diaz'
2)
DECLARE @P1 NVARCHAR(50)
SET @P1 = 'Diaz'
SELECT FirstName, LastName, PersonType, Title
FROM Person.Person
WHERE LastName = @P1
Now, I could not reproduce this (make DTA give me the same advice, to replace 1) with 2) ). Perhaps I misunderstood the poster in the group, it is because I'm running SQL Server 2008 DTA and engine, I'm not looking in the right place for that advice, my data isn't representative, I'm running the DTA with some other settings, etc.. But say that DTA does indeed give such an advice, would would it do that? To be honest, I don't know. It can hardly have enough information to determine whether 1) or 2) is the best choice.
In short: Say we have an index on the LastName column and the name we look for can either be a very common name, like perhaps "Smith", or a not so common name, like "Karaszi". For the more common name, a table scan might be the best choice, where for the not-so-common name, an index seek it probably best thing. OK, a very simple example, but serves well for this discussion.
Back to the difference between 1) and 2). There are potentially very important differences between the two:
For 1), SQL Server will determine a plan based on the contents of the parameter when the plan is created. I.e., it can determine selectivity based on that and determine whether it is a good idea to use an index or not. The plan is then cached and can be re-used. But what if we for the first execution pass in something which is very selective, but typically we are not very selective? Or the other way around? I,e, the plan for one case might not be optimal for some other case. This is where we have new optimizer hints in 2005 like OPTIMIZE FOR and the RECOMPILE hints. I will not go into details about these here, since I'm focusing on the differences between 1) and 2). See the white paper I mentioned, and of course Books Online, for more details.
For 2), SQL Server (or rather: the optimizer) has no knowledge of the contents of the variable when the plan is produced. So it cannot use the statistics to determine selectivity. In above case, it instead uses density (stored with the statistics, assuming such exists for the column). Density is basically the 1/ number of unique values for the column(s). This might be a good representation for a typical lastname, but perhaps not too good for a very common or a very uncommon lastname. Now, in 2005, we have hints for these situations as well (RECOMPILE), but again, that is not the point here.
In order for DTA to give the best advice here, it would have to know about the distribution over the data for that column and also have many executions of that query to see if "all" executions are using a typical value (sp_executesql might be better) or if the stored density value is a good representation for "all" queries that are passed from the clients. I very much doubt that DTA has this level of sophistication. Basically, I don't know why it advices this. There might be other aspects, like "avoid dynamic SQL" (which whether that holds for this case we could argue in another post), but DTA is about performance, not best practices.
Bottom line is that these things are not simple and we should be very cautious with "rules of thumbs".
Here's some TSQL code to demonstrate the differences between 1) and 2). As always, only execute after you read and understood the code! --Create a copy of the person table
--We will have lots of "Diaz" and very few "Gimmi"
IF OBJECT_ID('p') IS NOT NULL DROP TABLE p
GO
SELECT * INTO p
FROM Person.Person
CREATE INDEX x ON dbo.p(LastName)
--Create lots of Diaz
INSERT INTO p
SELECT BusinessEntityID + 30000, PersonType, NameStyle, Title, FirstName, MiddleName, N'Diaz', Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate
FROM p
--Make sure we have up-to-date statistics
UPDATE STATISTICS p WITH FULLSCAN
--Verify execution plan and I/O cost
--for table scan with low selectivity
--and index seek with high selectivity
SET STATISTICS IO ON
DBCC FREEPROCCACHE
SELECT * FROM p WHERE LastName = 'Diaz'
--20183 rows, table scan, 7612 pages
DBCC FREEPROCCACHE
SELECT * FROM p WHERE LastName = 'Gimmi'
--1 row, index seek, 3 pages
------------------------------------------------------------
--sp_execute alternative
------------------------------------------------------------
--Table scan will be used for both because of execution plan re-use
DBCC FREEPROCCACHE
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1',
N'@P1 nvarchar(50)', 'Diaz'
--20183 rows, table scan, 7612 pages
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1',
N'@P1 nvarchar(50)', 'Gimmi'
--1 row, table scan, 7612 pages
--Other way around
--Index search will be used for both because of execution plan re-use
DBCC FREEPROCCACHE
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1',
N'@P1 nvarchar(50)', 'Gimmi'
--1 row, index seek, 3 pages
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1',
N'@P1 nvarchar(50)', 'Diaz'
--20183 rows, index seek, 20291 pages
------------------------------------------------------------------
--Alternative using variable
------------------------------------------------------------------
DBCC FREEPROCCACHE
DECLARE @P1 NVARCHAR(50)
SET @P1 = 'Diaz'
SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1
GO
--20183 rows, index seek, 20291 pages
DBCC FREEPROCCACHE
DECLARE @P1 NVARCHAR(50)
SET @P1 = 'Gimmi'
SELECT FirstName, LastName, PersonType, Title
FROM p
WHERE LastName = @P1
GO
--1 rows, index seek, 1 pages
--Same plan even though very different selectivity
--and emptying plan cache in between
--Estimated 33 rows for both above.
--See if that is drawn from statistics density?
--Formula for density: 1/#OfUniqueValuesInColumn
SELECT 1./COUNT(DISTINCT LastName) FROM p
--0.00082918739
--Does that match density in index statistics?
DBCC SHOW_STATISTICS('p', 'x')
--Yes
--How many rows in the table?
SELECT COUNT(*) FROM p
--39944
--So how many rows would we estimate based on density?
SELECT 0.00082918739 * 39944
--Yep, 33 rows.
--I.e, for the variable alternative, SQL Server has no
--knowledge of contents of those variables so it must use density instead.
--Clean up:
IF OBJECT_ID('p') IS NOT NULL DROP TABLE p
|
-
I'm in a situation where I have configured the new data collector functionality for three instances. And there's no way to undo the config performed by the wizard! It cannot be undone by the wizard, and BOL doesn't have information on how to do this. In fact, I suspect that you in the need need to use some of the undocumented data collector procedures to get rid of this configuration (like sp_syscollector_delete_jobs).
I'm not nocking data collector per se - it seems like a great way to get a baseline going etc. But my tip is that while your are playing with it in order to understand it - do it virtually.
Lara has reported this on connect, btw: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=334180
|
-
Is it quicker and/or lower overhead to insert into a heap vs. a clustered table?
I don't know. So I decided to do a test. Some background information first:
The test was inspired from a sidebar with Gert-Jan Strik in the open newsgroups. Basically I expressed that a heap doesn't automatically carry lower overhead... just because it is a heap. Now, heaps vs. clustered tables is a huge topic with many aspects. I will not cover anything else here except inserts into a heap vs. a table which is clustered on an ever increasing key. No other indexes. There will be no fragmentation. I do not cover searches, covering etc. Only the pure insert aspect. OK? Good!
One might think that a heap has lower overhead because it is a ... heap. But hang on for a second and think about what happens when you do an insert:
Heap:
SQL Server need to find where the row should go. For this it uses one or more IAM pages for the heap, and it cross references these to one or more PFS pages for the database file(s). IMO, there should be potential for a noticable overhead here. And even more, with many users hammering the same table I can imagine blocking (waits) against the PFS and possibly also IAM pages.
Clustered table:
Now, this is dead simple. SQL server navigates the clustered index tree and find where the row should go. Since this is an ever increasing index key, each row will go to the end of the table (linked list).
The result:
So what is the conclusion? I did several executions of the code at the end of this post, with some variations. Basically there was no or very little difference whith only one user. I.e., no contention to the GAM or PFS pages. This was pretty consistent for below three scenarios:
- Insert with subselect. I.e., this inserts lots of rows in the same statement.
- Insert in a loop (one insert and row per iteration), many rows in the same transaction.
- Insert in a loop, one row per transaction.
Now the difference between 2 and 3 is important.
With many transactions, we incur an overhead of force-log-write-at-commit *for each row*. I.e., much more overhead against the transaction log. And indeed, the timings between 2 and 3 for one of my executions (10000 rows) showed that 2 took on average 650 ms where the same number for 3 was 5600 ms. This is about 9 times longer!!! Now, this was more or less expected, but another important aspect is when we have several users. With many users, we might run into blocking on the PFS and IAM pages. Also, with several users it is meaningless to do it all in one transaction since we will block and essentially single-thread the code anyhow. I.e., the only revelant measure where we run many users is the loop construction where each row is its own transaction (3).
There was indded a noticeable difference when I executed several inserts in parallell and had each insert in its own transaction (for clustered table vs. heap table).
Some numbers:
I did 4 repeated tests and calculated average execution time for inserting 10000 rows for a thread. With 6 parallel thread I had 22 seconds for a clustered table and 29 seconds for a heap table. With 10 threads I had 31 seconds for a clustered table and 42 seconds for a heap table.
I didn't find performance difference more than a couple of percents for batch inserts, when I single threaded (only one thread pumping inserts), or when I had all inserts in the loop as one transaction.
Now, I would need lots of more time to run exchaustive tests, but my interpretation is that with many users doing inserts, there is an noticable overhead for the heap vs clustering on a increasing key.
The code:
Note that for parallell executions, I recommend starting the DoTheInserts procedure using SQLCMD, a BAT file and START. As always, read the code carefully (so you understand it) and execute at your own risk.
--------------------------------------------
--Create the database etc.
--------------------------------------------
USE master SET NOCOUNT ON
GO
IF DB_ID('TestDb') IS NOT NULL DROP DATABASE TestDb
GO
--Makes files large enough so that inserts don't causes autogrow
CREATE DATABASE TestDb
ON PRIMARY
(NAME = 'TestDb', FILENAME = 'C:\TestDb.mdf', SIZE = 300MB, FILEGROWTH = 50MB)
LOG ON
(NAME = 'TestDb_log', FILENAME = 'C:\TestDb_log.ldf', SIZE = 200MB, FILEGROWTH = 100MB)
GO
--Full recovery to avoid effect of system caused log truncation
ALTER DATABASE TestDb SET RECOVERY FULL
BACKUP DATABASE TestDb TO DISK = 'nul'
USE TestDb
--Execution time log table
IF OBJECT_ID('TimeLogger') IS NOT NULL DROP TABLE TimeLogger
GO
CREATE TABLE TimeLogger
(
SomeId int identity
,spid int
,TableStructure varchar(10) CHECK (TableStructure IN ('heap', 'clustered'))
,InsertType varchar(20) CHECK (InsertType IN('one statement', 'loop'))
,ExecutionTimeMs int
)
GO
IF OBJECT_ID('RowsToInsert') IS NOT NULL DROP TABLE RowsToInsert
CREATE TABLE RowsToInsert(#rows int)
GO
--Support procedures
IF OBJECT_ID('CreateTables') IS NOT NULL DROP PROC CreateTables
GO
CREATE PROC CreateTables AS
IF OBJECT_ID('HeapLoop') IS NOT NULL DROP TABLE HeapLoop
CREATE TABLE HeapLoop(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
IF OBJECT_ID('ClusteredLoop') IS NOT NULL DROP TABLE ClusteredLoop
CREATE TABLE ClusteredLoop(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
CREATE CLUSTERED INDEX x ON ClusteredLoop(c1)
IF OBJECT_ID('HeapOneStatement') IS NOT NULL DROP TABLE HeapOneStatement
CREATE TABLE HeapOneStatement(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
IF OBJECT_ID('ClusteredOneStatement') IS NOT NULL DROP TABLE ClusteredOneStatement
CREATE TABLE ClusteredOneStatement(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
CREATE CLUSTERED INDEX x ON ClusteredOneStatement(c1)
GO
IF OBJECT_ID('TruncateTables') IS NOT NULL DROP PROC TruncateTables
GO
CREATE PROC TruncateTables AS
TRUNCATE TABLE HeapLoop
TRUNCATE TABLE ClusteredLoop
TRUNCATE TABLE HeapOneStatement
TRUNCATE TABLE ClusteredOneStatement
GO
IF OBJECT_ID('DoBefore') IS NOT NULL DROP PROC DoBefore
GO
CREATE PROC DoBefore AS
BACKUP LOG TestDb TO DISK = 'nul'
CHECKPOINT
GO
IF OBJECT_ID('iHeapLoop') IS NOT NULL DROP PROC iHeapLoop
GO
CREATE PROC iHeapLoop @rows int AS
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
INSERT INTO HeapLoop (c2) VALUES(2)
SET @i = @i + 1
END
GO
IF OBJECT_ID('iClusteredLoop') IS NOT NULL DROP PROC iClusteredLoop
GO
CREATE PROC iClusteredLoop @rows int AS
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
INSERT INTO ClusteredLoop (c2) VALUES(2)
SET @i = @i + 1
END
GO
IF OBJECT_ID('iHeapOneStatement') IS NOT NULL DROP PROC iHeapOneStatement
GO
CREATE PROC iHeapOneStatement @rows int AS
INSERT INTO HeapOneStatement (c2)
SELECT TOP(@rows) 2 FROM syscolumns a CROSS JOIN syscolumns b
GO
IF OBJECT_ID('iClusteredOneStatement') IS NOT NULL DROP PROC iClusteredOneStatement
GO
CREATE PROC iClusteredOneStatement @rows int AS
INSERT INTO ClusteredOneStatement (c2)
SELECT TOP(@rows) 2 FROM syscolumns a CROSS JOIN syscolumns b
GO
--Proc to do the inserts
IF OBJECT_ID('DoTheInserts') IS NOT NULL DROP PROC DoTheInserts
GO
CREATE PROC DoTheInserts
AS
DECLARE @dt datetime, @NumberOfRowsToInsert int
SET @NumberOfRowsToInsert = (SELECT #rows FROM RowsToInsert)
EXEC DoBefore --Batch allocation, heap:
SET @dt = GETDATE()
EXEC iHeapOneStatement @rows = @NumberOfRowsToInsert
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'heap', 'one statement', DATEDIFF(ms, @dt, GETDATE()))
EXEC DoBefore --Batch allocation, clustered:
SET @dt = GETDATE()
EXEC iClusteredOneStatement @rows = @NumberOfRowsToInsert
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'clustered', 'one statement', DATEDIFF(ms, @dt, GETDATE()))
EXEC DoBefore --Single allocations, heap:
SET @dt = GETDATE()
--BEGIN TRAN
EXEC iHeapLoop @rows = @NumberOfRowsToInsert
--COMMIT
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'heap', 'loop', DATEDIFF(ms, @dt, GETDATE()))
EXEC DoBefore --Single allocations, clustered
SET @dt = GETDATE()
--BEGIN TRAN
EXEC iClusteredLoop @rows = @NumberOfRowsToInsert
--COMMIT
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'clustered', 'loop', DATEDIFF(ms, @dt, GETDATE()))
GO
--Run the tests
EXEC CreateTables
TRUNCATE TABLE TimeLogger
TRUNCATE TABLE RowsToInsert INSERT INTO RowsToInsert VALUES(10000)
--<Below can be executed over several connections>
EXEC DoTheInserts
EXEC DoTheInserts
EXEC DoTheInserts
EXEC DoTheInserts
--</Below can be executed over several connections>
--How did we do?
SELECT COUNT(*) AS NumberOfExecutions, TableStructure, InsertType, AVG(ExecutionTimeMs) AS AvgMs
FROM TimeLogger WITH(NOLOCK)
GROUP BY TableStructure, InsertType
ORDER BY InsertType, TableStructure
--Verify that no fragmentation
SELECT
OBJECT_NAME(OBJECT_ID) AS objName
,index_type_desc
,avg_fragmentation_in_percent AS frag
,page_count AS #pages
,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME(OBJECT_ID) <> 'TimeLogger' AND index_level = 0
|
-
-
(See my initial blog post for general information about this proc.)
I just updated sp_indexinfo a bit:
- I added the schema name as a new column in the output of the first resultset.
- I added an optional second resultset with missing index information. This information is obviously drawn from the missing index dynamic management views. I'm not sure I generated the CREATE INDEX statment properly (the equality and inequality columns) since I didn't have much missing index entries to play with at the moment. All tests and replies are much welcome ("work fine" - "doesn't work" - "doesn't work because of..." - "change aaa to bbb" etc.).
I also updated the web site with some tips on creating a view in the databases where you want to work a lot so you can select from this view, aggregate etc. I will try to as much as possible stick with less procedural code and more set-based code. My aim is to have perhaps only two SELECT statements in the proc, so we can just take a SELECT statement, create a view or function inside your database and work with it from there. So, for instance, I will probably not add support for specify 'schemaname.tablename' for the first parameter as that will probably require some procedural parsing code (as seen in sp_helpindex). There's a trade-off between all the nice things you can complement using procedural code and having all in one or a few SELECT statments and being able to simply creating a view or table function from those.
One possible enhancement is to add fragmentation information. For this I will need to perform some type of join or subquery against sys.dm_db_index_physical_stats. My concern here is the cases where you don't want this information (because of cost for large tables/databases). Ideally I don't want two versions of the query (see above paragraph) but I also don't want to pay the penalty in cases where we don't want this info. Possibly this is doable using some correlated subquery as a column together with CASE, but this is only in my mind yet. Suggestions and thoughts are welcome.
You find the proc at: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
|
|
|
|