November 16, 2011 at 12:54 pm
fhanlon (11/16/2011)
I like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105.
I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.
Yes, you are right - but not for the reasons yoou mention.
When I wrote the question, I was afraid that there would be discussion over fragmentation that occurs while filling the table. To prevent that, I added the index rebuild step - and completely forgot that during the rebuild, the index temporarily exists twice, doubling the space requirement. After the index rebuild is finished, the numbers are correct. And if I had specified a larger data file (3.5 GB or so), the error would not have occured.
November 16, 2011 at 1:03 pm
Hugo Kornelis (11/16/2011)
Even after rereading the entire discussion, I fail to see wherer others point out errors that relate to the data page size. Can you elaborate, please?
I was refering to where you had 20,000 instead of 200,000 or whatever.
Not at all! Just after the data insert, the information from this DMV would be highly skewed. In a real-world system (which this is not!), you could indeed get some useful information from the missing indexes DMVs - but only after letting it run for a while, or running a representative test. This view is added to when the optimizer compiles a query that could benefit from an index that doesn't exist. These suggestions are only for a single query. If you plan to use this DMV for real tuning, try to combine the various suggestions into a single index that helps lots of queries. And never forget that these suggestions give an indication of estimated saving for a single query, but do not take into account how the extra index might harm performance of inserts, updates, deletes, and merges.
You are absolutely right. Just trying to point out the DMV used to find the columns of a good NONCLUSTERED index.
The Books Online article you link to does try to explain this, but does not really phrase this well. There is a much better explanation in the CREATE TABLE article at http://msdn.microsoft.com/en-us/library/ms174979.aspx, where it reads:
"CLUSTERED | NONCLUSTERED
Completely agreed. Again someone else mentioned this to me. We both thought before today that when creating a Primary Key constaring the Index could only be CLUSTERED unless a clustered Index existed already.
As far as I see, this is exactly what I say in my explanation - in the absence of an explicit CLUSTERED or NONCLUSTERED keyword, the index for the primary key defaults to clustered (unless another index is specified as clustered - an edge case that I chose not to include in the explanation as it does not apply here and seldom -if ever!- occurs in real world situations).
Apparently so! The edge case I have never seen in the real world is a table with a single nonclustered index on the primary key. I seriously thought this was not even possible. So much to still learn about what is good, and what is possible, and why.
Thank you both Hugo and Jeff.
Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?
November 16, 2011 at 1:10 pm
Hugo Kornelis (11/16/2011)
SanDroid (11/16/2011)
I was editing my original post while you read and resonded to this. The original post did not comunicate what I intended.No problem, that can happen. I'll go back to the revised version of your message.
The tables Primary Key Clustered Index is not at all related to the data stored in the table or to the 2 Million new rows being inserted into it.
That's just an assumption. The question contains no information to confirm or deny this theory.
Very true. That assumption was made after you told somone that asked you what values should be put into the Primary Key since that was missing from the QOTD when you mentioned the data that would be inserted. You mentioned the char column could be generated random values. I belived you meant generated random value by the insert statement code, not by the "business" prior to the insert. That makes a lot more sense and has the perfect level of detail.
Thank you for clarifying.
November 16, 2011 at 1:30 pm
Hugo Kornelis (11/16/2011)
fhanlon (11/16/2011)
I like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105.
I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.
Yes, you are right - but not for the reasons yoou mention.
When I wrote the question, I was afraid that there would be discussion over fragmentation that occurs while filling the table. To prevent that, I added the index rebuild step - and completely forgot that during the rebuild, the index temporarily exists twice, doubling the space requirement. After the index rebuild is finished, the numbers are correct. And if I had specified a larger data file (3.5 GB or so), the error would not have occured.
So now I am confused again.
Are the 47% that selected this answer to the question Right or Wrong? 😛
November 16, 2011 at 1:30 pm
SanDroid (11/16/2011)
Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?
If you are running the code with the given parameters, you'll get that error because of the reindex. That's why I missed this question when I answered it -- I chose "error" because if you calculate the size of the 1 million rows, they are bigger than 1 GB. And since a reindex makes a complete copy of the table for the reindex process (assuming you have a clustered index, which is why the clustering matters), that means you need more than 2 GB and the question fails as structured. Hugo did a good job of catching that early and stating his mistake.
November 16, 2011 at 1:33 pm
jeff.mason (11/16/2011)
SanDroid (11/16/2011)
Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?If you are running the code with the given parameters, you'll get that error because of the reindex. That's why I missed this question when I answered it -- I chose "error" because if you calculate the size of the 1 million rows, they are bigger than 1 GB. And since a reindex makes a complete copy of the table for the reindex process (assuming you have a clustered index, which is why the clustering matters), that means you need more than 2 GB and the question fails as structured. Hugo did a good job of catching that early and stating his mistake.
Exactly what I thought. But since my 2008 R2 test system has row compression enabled as a default I was trying to determine if it was that or just the reindex alone that caused it.
Thanks again Jeff! 😎
November 16, 2011 at 1:37 pm
I guess the bigest thing I learned from the QOD today is to make certain you have done your research and fully tested what you are saying BEFORE telling someone they are wrong.
November 16, 2011 at 1:40 pm
What's nice is that no one got out of control, angry, or vindictive. A good exchange with a positive outcome is a good thing.
November 16, 2011 at 1:43 pm
jeff.mason (11/16/2011)
What's nice is that no one got out of control, angry, or vindictive. A good exchange with a positive outcome is a good thing.
Jeff. That is very very true. And why this is now one of my favorite QOD discussions EVER!
Thanks again HUGO. Your question might have been a little busted, but the learning and dicussion it inspired was definately not. :smooooth:
November 16, 2011 at 1:50 pm
Hugo Kornelis (11/16/2011)
My question is: would you like a few more questions of this kind, or would submitting the rest of the questions I was planning expose me to death threats, stale fruit, or lots of boo-ing and hissing?This is a serious question. If the majority feels that the QotD should not involve that much work, I will not submit the other similar questions I was planning.
Hugo, please more!
Just please next time use the same numbers in the QOTD that you use when doing your reserach. 😉
Obviously lots of people learned today, including you. Lets have some more. It could even be worth some of the stale fruit you might recieve. :hehe:
November 16, 2011 at 1:51 pm
After doing the calculation I was tempted to answer the queston but to be sure I ran the script and got the error:
The statement has been terminated.
Msg 1101, Level 17, State 12, Line 2
Could not allocate a new page for database 'QOTD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Now the script I ran is
Creating the database:
CREATE DATABASE [QOTD] ON PRIMARY
(
NAME = N'QOTD'
, FILENAME = N'...\QOTD.mdf'
, SIZE = 2097152KB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 0
)
LOG ON
(
NAME = N'QOTD_log'
, FILENAME = N'...\QOTD_log.ldf'
, SIZE = 1024KB
, MAXSIZE = 2048GB
, FILEGROWTH = 10%
)
GO
ALTER DATABASE [QOTD] SET COMPATIBILITY_LEVEL = 100
GO
Then creating the table:
USE [QOTD]
GO
CREATE TABLE dbo.DemoTable
(DemoTableKey char(10) NOT NULL,
LargeNumber bigint NOT NULL,
ShortDescription char(80) NOT NULL,
LongDescription nchar(600) NULL,
Price money NOT NULL,
Counter1 int NOT NULL,
Counter2 bigint NOT NULL,
Counter3 smallint NOT NULL,
Counter4 int NOT NULL,
AddDate datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),
ChgDate datetime NULL,
CONSTRAINT PK_DemoTable PRIMARY KEY (DemoTableKey),
CONSTRAINT CK_LargeNumber CHECK (LargeNumber > 0)
);
GO
Now filling the 1 mil records:
; WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 lines
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 lines
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 lines
E5(N) AS (SELECT 1 FROM E4 a, E2 b) --10E+6 or 1,000,000
INSERT INTO [dbo].[DemoTable]
([DemoTableKey]
,[LargeNumber]
,[ShortDescription]
,[LongDescription]
,[Price]
,[Counter1]
,[Counter2]
,[Counter3]
,[Counter4]
-- ,[AddDate]
,[ChgDate])
SELECTROW_NUMBER() OVER (ORDER BY N)
, 1
, 'NONE'
, NULL
, 1
, 1
, 1
, 1
, 1
-- , -- DEFAULT
, NULL
FROM E5
GO
Everyting is OK so far, all ran successfuly.
Finaly rebuilding the index:
ALTER INDEX ALL ON dbo.DemoTable
REBUILD WITH (FILLFACTOR = 100,
DATA_COMPRESSION = NONE,
MAXDOP = 1);
-- TRUNCATE TABLE dbo.DemoTable
I am not worried about the lost point, but is there anything I have missed?
One mention I did not get the error after the INSERT statement but after running the ALTER INDEX statement.
The question is great, a strong question, I would like to see more like this.
Thank you,
Iulian
November 16, 2011 at 2:14 pm
SanDroid (11/16/2011)
So now I am confused again.Are the 47% that selected this answer to the question Right or Wrong? 😛
If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).
However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.
November 16, 2011 at 2:16 pm
SanDroid (11/16/2011)
Obviously lots of people learned today, including you. Lets have some more. It could even be worth some of the stale fruit you might recieve. :hehe:
No stale fruit today, but a very interesting discussion where we all learned. I definitely took away some things to consider for my next questions! There might be errors in those as well, but I'll do my very best to make sure that they will at least be NEW errors! 😉
November 16, 2011 at 2:16 pm
Hugo Kornelis (11/16/2011)
SanDroid (11/16/2011)
So now I am confused again.Are the 47% that selected this answer to the question Right or Wrong? 😛
If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).
However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.
I am certain Steve will correct me if I am wrong, but I think the "software" has the ability to do exactly that. Maybe you should ask him... :w00t:
November 16, 2011 at 2:25 pm
Iulian -207023 (11/16/2011)
I am not worried about the lost point, but is there anything I have missed?
No, that was my mistake. I added the index rebuild to the question to make sure there was no fragmentation, regardless of how the data was filled. But I forgot that rebuilding an index required SQL Server to temporarily duplicate it - so during the rebuild, you need twice as much space for the table. That's a little over 3 GB.
I should have tested with the given amount of data. But I wanted to save time, so I tested with one tenth and checked that all the calculation and formulas I had in my spreadsheet exactly predicted the amount of pages used. I was then confident that my date size calculations would be correct for the full million rows as well. Which in fact they were - if I had specified the size of the data file as 3.5GB or so, there would not have been any problem.
(Funny side story - just to be sure, I just now did run the tests for the full million rows. First with a 2GB data file, then with a 3.5GB data file. To my utter surprise, the index rebuild did NOT produce an error when I tested with the 2GB data file. It took me some time before I found the cause - a missing USE statement. I now have a very bloated master database... :Whistling:)
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply