Query on a large database
-
Ah... to be sure... if partitioning could be done to separate disks, it still might not help. While I agree that bringing more spindles (and, therefor, read/write heads and parallel paths into play) can help any situation, it's just as likely that a monolithic table that straddles many disks would actually be faster because you wouldn't have all the same time or age periods on a single set of read/write heads. Even with that, it would likely only help the first run because the data must be in memory for SQL Server to use it. Once cached, subsequent runs wouldn't bring spindles into the game at all. That would be another interesting test to do provided that we didn't clear cache for every iteration.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (10/31/2015)
Not to mention, that's a really nice machine you've got there, Sergiy.It's not a nice machine, it's an empty recordset returned.
Which is actually a very good test - how long does it take to find out that there is nothing to return.
And it's getting worse if we remind ourselves about another kind of statistics:
set statistics io onLook what I've got for non-partitioned table with clustered index:
Table 'unpartitionedtbl'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.And now look at this:
Table 'partitionedtbl'. Scan count 9, logical reads 640925, physical reads 247, read-ahead reads 640523, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Gosh, 9 scans, over half a million reads - just to find out that there are no records matching the criteria????
_____________
Code for TallyGenerator -
An empty rowset return would certainly explain the 0 CPU time but I thought you said you loaded both tables. There are a pretty good number of rows in the 8 million in the test for 27, 28, and 29. No 30's though.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Shifting gears, I'm adding an aligned clustered index (same key as the nonpartitioned table) to the partitioned table to test the other possibility, which is also what I've done for partitioned tables at work. We'll see how the same tests fair then.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (11/1/2015)
@Sergiy,An empty rowset return would certainly explain the 0 CPU time but I thought you said you loaded both tables. There are a pretty good number of rows in the 8 million in the test for 27, 28, and 29. No 30's though.
Ah... how soon I forgot the problem with the double-quotes in the CSV file. If you loaded the data without modifying the delimiter in the BULK INSERT in the originally provided code, there are no 27's, 28's, or 29's without the double quotes and, therefore, would return an empty result set when you looked for them without the double quotes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Ok... as a bit of final proof that partitioning does very little to improve performance and can make it a bit worse, I added the following non-unique clustered index fully aligned with the partitioning present on the partitioned table. (I did it in the SIMPLE recovery model to the index build would be minimally logged for reasons of performance. Still took 8:11 to finish and, of course, doubled the size of the space used in the MDF and NDF files. The space was returned as free space when done but it's still allocated to the MDF and NDF files. More onn that later.)
CREATE CLUSTERED INDEX IXUCP_partitionedtbl_REF_AGE
ON dbo.partitionedtbl (REF_AGE)
WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF, FILLFACTOR = 100)
ON PARTITIONSCHEME_1 (REF_AGE)
;
Then, I ran the same test for only the single cache clear per run as we did before...
PRINT '========== Partitioned table, drop buffers only on first iteration. ===================='
GO
dbcc dropcleanbuffers
GO --Added this
set statistics time on
Select REF_AGE , COUNT(*)
from Partitiontest123.dbo.partitionedtbl
where ref_age between '27' and '30'
group by REF_AGE
set statistics time off
GO 5
PRINT '========== NON Partitioned table, drop buffers only on first iteration. ===================='
GO
dbcc dropcleanbuffers
GO --Added this
set statistics time on
Select REF_AGE , COUNT(*)
from Partitiontest122.dbo.unpartitionedtbl
where ref_age between '27' and '30'
group by REF_AGE
set statistics time off
GO 5
Here are the cleaned up results from that...
========== Partitioned table, drop buffers only on first iteration. ====================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
CPU time = 984 ms, elapsed time = 16582 ms.
CPU time = 935 ms, elapsed time = 366 ms.
CPU time = 998 ms, elapsed time = 368 ms.
CPU time = 968 ms, elapsed time = 358 ms.
CPU time = 858 ms, elapsed time = 377 ms.
Batch execution completed 5 times.
========== NON Partitioned table, drop buffers only on first iteration. ====================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
CPU time = 890 ms, elapsed time = 23773 ms.
CPU time = 1169 ms, elapsed time = 350 ms.
CPU time = 1248 ms, elapsed time = 312 ms.
CPU time = 982 ms, elapsed time = 322 ms.
CPU time = 1248 ms, elapsed time = 312 ms.
Batch execution completed 5 times.
What's immediately apparent is that the duration on the partitioned table is almost as good as the non partitioned table. CPU also came down to be a little better than the non-partitioned table. The reason for that may actually be a little "partition elimination". Here's the code I ran with STATISTICS IO turned on instead of STATISTICS TIME.
PRINT '========== Partitioned table, drop buffers only on first iteration. ===================='
GO
dbcc dropcleanbuffers
GO
set statistics IO on
Select REF_AGE , COUNT(*)
from Partitiontest123.dbo.partitionedtbl
where ref_age between '27' and '30'
group by REF_AGE
set statistics IO off
GO 5
PRINT '========== NON Partitioned table, drop buffers only on first iteration. ===================='
GO
dbcc dropcleanbuffers
GO
set statistics IO on
Select REF_AGE , COUNT(*)
from Partitiontest122.dbo.unpartitionedtbl
where ref_age between '27' and '30'
group by REF_AGE
set statistics IO off
GO 5
And here are the results from that. Like I said, small advantage in the number of reads for partitioning, in this case, likely resulted in the small gain in performance CPU wise.
========== Partitioned table, drop buffers only on first iteration. ====================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
(3 row(s) affected)
Table 'partitionedtbl'. Scan count 4, logical reads 175916, physical reads 122, read-ahead reads 175904, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'partitionedtbl'. Scan count 4, logical reads 175916, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'partitionedtbl'. Scan count 4, logical reads 175916, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'partitionedtbl'. Scan count 4, logical reads 175916, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'partitionedtbl'. Scan count 4, logical reads 175916, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Batch execution completed 5 times.
========== NON Partitioned table, drop buffers only on first iteration. ====================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
(3 row(s) affected)
Table 'unpartitionedtbl'. Scan count 5, logical reads 176540, physical reads 2682, read-ahead reads 175902, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'unpartitionedtbl'. Scan count 5, logical reads 178530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'unpartitionedtbl'. Scan count 5, logical reads 178530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'unpartitionedtbl'. Scan count 5, logical reads 178530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 row(s) affected)
Table 'unpartitionedtbl'. Scan count 5, logical reads 178530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Batch execution completed 5 times.
To summarize it all, the partitioning by itself was twice as bad as the monolithic table with the index on it. Adding a similar index to the partitioned table made the partitioned table operate more closely to the monolithic indexed table. Partitioning with the index did NOT beat the monolithic indexed table for duration performance but did help a bit for CPU performance.
The bottom line is that partitioning did virtually nothing for performance and what most of us have claimed is true. "Performance of {read} code is not a reason to partition". The jury is still out on {write} performance and I'll test that soon.
The only reason to partition is to reduce index maintenance and, possibly (it worked great for me at work), to reduce backup times on temporally based tables (such as audit tables) simply because the older months don't suffer changes on such tables and, therefor, the older months (usually all but the current month) don't need to be backed up.
Making the older months Read-Only is something that helps a bit with Piece-Meal restores but there are huge caveats to make that work without making large amounts of freespace Read-Only (I've given several presentations on how to do that an am currently working on an article for it).
Like I've said many times, if you think the partitioning you did was easy, you've probably done something wrong. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (11/1/2015)[hrAh... how soon I forgot the problem with the double-quotes in the CSV file. If you loaded the data without modifying the delimiter in the BULK INSERT in the originally provided code, there are no 27's, 28's, or 29's without the double quotes and, therefore, would return an empty result set when you looked for them without the double quotes.
Yes, you've guessed right.
At first I thought of it as a mistake, but then I figured - it's a fortunate misfortune.
Looking for missing data indicates the effectiveness of the methods of identifying "records to read" used in both cases.
Anything after that may be affected by a number of factors (including other search conditions, or OS file fragmentation) which are beyond our control and may only distract from the actual comparison results.
_____________
Code for TallyGenerator -
Thanks he confirmation, Sergiy,
Just to put a wrapper on this, I ran the following code to test for writes with the understanding that I've only got one disk on my laptop. Of course, good luck getting SAN folks to give you multiple drives with guaranteed different spindles with nothing else running on them so this test may reflect real life more often than not.
PRINT '========== Truncate the partitioned table and load the file 3x =========='
TRUNCATE TABLE Partitiontest123.dbo.[partitionedtbl]
GO
BULK INSERT Partitiontest123.dbo.[partitionedtbl]
FROM 'C:\Temp\Jaynath\Exportdata.csv'
WITH
(
FIELDTERMINATOR ='","',
ROWTERMINATOR = ''
);
GO 3
PRINT '========== Truncate the NON partitioned table and load the file 3X =========='
TRUNCATE TABLE Partitiontest122.dbo.unpartitionedtbl
GO
BULK INSERT Partitiontest122.dbo.unpartitionedtbl
FROM 'C:\Temp\Jaynath\Exportdata.csv'
WITH
(
FIELDTERMINATOR ='","',
ROWTERMINATOR = ''
);
GO 3
To see the writes, I ran this during an SQL Profiler run. Here are the results. Nothing Earth shattering. The monolithic table did a couple of seconds better on each run but also used more reads. As a reminder, both the partitioned and non-partitioned tables have a clustered index, as before.
Also as expected, the 1st level (level closest to the Leaf Level) of the B-Tree takes a beating in percent of page used for both but that only represents about 8MB total or a waste of about 4MB.
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('PartitionTest123'),NULL,NULL,NULL,'DETAILED');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('PartitionTest122'),NULL,NULL,NULL,'DETAILED');
Again, the bottom line is that for most folks, partitioning isn't a performance advantage except when it comes to maintenance and then only for certain types of temporally based tables such as audit tables and invoice tables (etc).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Not to mention another "little" problem with partitioning:
Msg 7736, Level 16, State 1, Line 2Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.
_____________
Code for TallyGenerator -
Sergiy (11/1/2015)
Not to mention another "little" problem with partitioning:Msg 7736, Level 16, State 1, Line 2Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.
Heh... yep. Bit of a problem for most folks there. You can do Partitioned Views in the Standard Edition (I actually prefer partitioned views over partitioned tables) but they do have other caveats (all columns must be inserted unless you do your inserts through something like a pass-through view). I haven't tested performance for such a thing and not going to start today :hehe: . I'll "wait for the movie". 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (11/1/2015)
The only reason to partition is to reduce index maintenance and, possibly (it worked great for me at work), to reduce backup times on temporally based tables (such as audit tables)Also fast data loads, fast deletes, aka the sliding window.
The one time I implemented partitioning, it was for that. Was a lot of work to implement, test and get all the scheduled jobs working correctly. Once it was all working, it worked well, just a lot of up-front work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/1/2015)
Also fast data loads, fast deletes, aka the sliding window.The thing is - correct cluster index eliminates the necessity for a sliding window altogether.
_____________
Code for TallyGenerator -
Sergiy (11/1/2015)
GilaMonster (11/1/2015)
Also fast data loads, fast deletes, aka the sliding window.The thing is - correct cluster index eliminates the necessity for a sliding window altogether.
I'll have to say "It Depends". It's much faster to SPLIT and drop a partition of, say, several million rows, than it is to delete them even if the cluster is sequentially temporal or otherwise ever increasing. You can also take your time building and loading up a new partition and then MERGE it into place in the proverbial blink of an eye.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
GilaMonster (11/1/2015)
Jeff Moden (11/1/2015)
The only reason to partition is to reduce index maintenance and, possibly (it worked great for me at work), to reduce backup times on temporally based tables (such as audit tables)Also fast data loads, fast deletes, aka the sliding window.
The one time I implemented partitioning, it was for that. Was a lot of work to implement, test and get all the scheduled jobs working correctly. Once it was all working, it worked well, just a lot of up-front work.
Amen to that. I'd forgotten those advantages because we don't need "online bulk loads" and they never let me delete or even archive stuff at work, but you're absolutely spot on for both.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sergiy (11/1/2015)
GilaMonster (11/1/2015)
Also fast data loads, fast deletes, aka the sliding window.The thing is - correct cluster index eliminates the necessity for a sliding window altogether.
I disagree.
Deleting 20 million rows from a table of ~100 million rows cannot be done in milliseconds, no matter what clustered index you have, and it's going to escalate to table locks, long duration table locks, unless it's done in batches. The ALTER TABLE ... SWITCH is a ddl operation, it's very, very fast because all that's changing is the allocation information.
Switch out, truncate also means far less impact on the transaction log.
Loading's not as much of a hassle, but still to be able to bulk load (minimally logged under bulk-logged) into a staging table, build indexes on that without interfering with regular user operations and then to be able to switch the staging table into the main table in a very short time can be quite a large advantage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 76 through 90 (of 104 total)
You must be logged in to reply to this topic. Login to reply