April 12, 2011 at 11:32 am
I've run this 3-4 times now over the whole day and the results don't change. I'd use for XML instead of concat, which runs instantly, but our tables names are really weird and they use non xml-friendly characters.
Can anyone explain this behavior??? I've not seen any locking on sp_who2 that could explain this. No open tran at all.
SELECT * FROM sys.tables
/*
(8323 row(s) affected)
Table 'sysidxstats'. Scan count 1, logical reads 715, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syspalnames'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 413, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 232 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/
DECLARE @Exec VARCHAR(MAX)
SET @Exec = ''
SELECT @Exec = @Exec + 'UPDATE STATISTICS dbo.[' + name + '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) FROM sys.tables
PRINT @Exec
/*
Table 'Worktable'. Scan count 0, logical reads 237019, physical reads 0, read-ahead reads 0, lob logical reads 68655900, lob physical reads 0, lob read-ahead reads 1685971.
Table 'sysschobjs'. Scan count 1, logical reads 413, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 120 625 ms, elapsed time = 124 326 ms.
*/
April 12, 2011 at 11:39 am
String manipulation in windows is notoriously bad. XML is probably using the .net stringbuilder, which is supposed to be very efficient. A while back, I think Jeff compared it to greased lightening.
There are ways to get the xml version to work with your non-friendly table names - it's usually as simple as using the TYPE directive after the FOR XML PATH(''). Post what you've got for the XML version, and we'll get it working for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 12, 2011 at 11:43 am
I don't have a complete list of bad characters and I've got no idea about that type thing you're asking about.
All I know for sure at this point is that I need to handle those characters : <>&.
Maybe there are others but I want to take the guess work out of this. I have a massive window tonight so I want to test out long it takes to update all stats without discrimenations.
April 12, 2011 at 11:49 am
On a separate note, is this worth opening a connect ticket with ms for this issue?
My guess is that they'll just tell me to use a cursor...
April 12, 2011 at 12:03 pm
I'm curious. Why does a simple concat need variable = variable + string logic? I only ever use that when I'm COALESCING or looping (Cursor or While).
What are you trying to achieve? <- Stupid question. I see what the code does when I run it. I guess I learn something new every day. @=)
EDIT: When I run your code, it runs instantly with 00:00 seconds listed as query time in bottom corner of SSMS. But I don't have funny characters in my table names.
April 12, 2011 at 12:08 pm
Hey, Ninja, I just realized that Varchar(MAX) isn't big enough to handle a database with a lot of tables. I've tried it on two of my "larger" databases (object-wise, not size-wise) and the last statement gets cut off in the middle. I wonder if you're having the same issue.
April 12, 2011 at 12:13 pm
Brandie Tarvin (4/12/2011)
Hey, Ninja, I just realized that Varchar(MAX) isn't big enough to handle a database with a lot of tables. I've tried it on two of my "larger" databases (object-wise, not size-wise) and the last statement gets cut off in the middle. I wonder if you're having the same issue.
Is that happening when displaying the results back in the grid?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 12:16 pm
CirquedeSQLeil (4/12/2011)
Brandie Tarvin (4/12/2011)
Hey, Ninja, I just realized that Varchar(MAX) isn't big enough to handle a database with a lot of tables. I've tried it on two of my "larger" databases (object-wise, not size-wise) and the last statement gets cut off in the middle. I wonder if you're having the same issue.Is that happening when displaying the results back in the grid?
Yes.
April 12, 2011 at 12:19 pm
Brandie Tarvin (4/12/2011)
CirquedeSQLeil (4/12/2011)
Brandie Tarvin (4/12/2011)
Hey, Ninja, I just realized that Varchar(MAX) isn't big enough to handle a database with a lot of tables. I've tried it on two of my "larger" databases (object-wise, not size-wise) and the last statement gets cut off in the middle. I wonder if you're having the same issue.Is that happening when displaying the results back in the grid?
Yes.
That's an SSMS setting rather than a varchar(max) limitation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 12:26 pm
I may be missing something obvious here, but the name of the table comes from sys.tables, and that's NVARCHAR, not VARCHAR. Regardless of what your tables are named an NVARCHAR should work with no issues.
Have you tried ...
DECLARE @Exec NVARCHAR(MAX)
SET @Exec = N''
SELECT @Exec = @Exec + N'UPDATE STATISTICS dbo.[' + name + N'] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) FROM sys.tables
PRINT @Exec
**EDIT**
Sorry Ninja, I slowed down and RE-read your post and realized, it's not an issue of it "working" or "not" rather a perf question.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 1:15 pm
Ninja's_RGR'us (4/12/2011)
I don't have a complete list of bad characters and I've got no idea about that type thing you're asking about.All I know for sure at this point is that I need to handle those characters : <>&.
Maybe there are others but I want to take the guess work out of this. I have a massive window tonight so I want to test out long it takes to update all stats without discrimenations.
This is the generally accepted method for using XML in the presence of "bad" characters:
DECLARE @Exec NVARCHAR(MAX)
SELECT @Exec = (
SELECT N'UPDATE STATISTICS dbo.[' + name + N'] WITH FULLSCAN ; '+ NCHAR(13) + NCHAR(10)
FROM sys.tables
FOR XML PATH(''),TYPE
).value('(./text())[1]','nvarchar(max)')
As a sidebar, I don't encounter the same problem on 2k5.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2011 at 1:35 pm
Ninja's_RGR'us (4/12/2011)
I don't have a complete list of bad characters and I've got no idea about that type thing you're asking about.All I know for sure at this point is that I need to handle those characters : <>&.
Maybe there are others but I want to take the guess work out of this. I have a massive window tonight so I want to test out long it takes to update all stats without discrimenations.
Changing "FOR XML PATH('')" to "FOR XML PATH(''), TYPE" will automagically handle those characters.
Edit: Just read Jeff's post above - he posted the rest of the solution.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 12, 2011 at 1:49 pm
Ninja's_RGR'us (4/12/2011)
Can anyone explain this behavior??? I've not seen any locking on sp_who2 that could explain this. No open tran at all.
From testing I've found out that assigning more that 512KB of data to a [n]varchar(max) will cause SQL Server to spill the variable to tempdb. Query sys.dm_db_session_file_usage (or was it sys.dm_db_task_space_usage) before and after you have executed your code and see if the internal_objects_alloc_page_count has changed for your session.
April 12, 2011 at 5:37 pm
Thank you all. I'm off site and I'll have to wait tomorrow to test Jeff's version of the code. Since "my" for xml was running in 16 ms I'm not too worried. I just didn't know how to make it work with the weird table names!
As for the tempdb spill it's definitely a possibility. Just look at the work table stats... over 70 MILLION reads for a freaking string!!! That's over half a TB of processed data :w00t:.
Table 'Worktable'. Scan count 0, logical reads 237 019, physical reads 0, read-ahead reads 0, lob logical reads 68 655 900, lob physical reads 0, lob read-ahead reads 1 685 971.
Now for a last question. Is this worth adding has a connect ticket??? There's slow and then there's this :hehe:! I'll check the final code length but I'm pretty sure it's under 1 MB. 2 at the very worst.
SQL server 2005 32 bit std SP4 SP3.
Edited SP version in case it's relevant.
April 12, 2011 at 5:41 pm
Jeff Moden (4/12/2011)
Ninja's_RGR'us (4/12/2011)
I don't have a complete list of bad characters and I've got no idea about that type thing you're asking about.All I know for sure at this point is that I need to handle those characters : <>&.
Maybe there are others but I want to take the guess work out of this. I have a massive window tonight so I want to test out long it takes to update all stats without discrimenations.
This is the generally accepted method for using XML in the presence of "bad" characters:
DECLARE @Exec NVARCHAR(MAX)SELECT @Exec = (
SELECT N'UPDATE STATISTICS dbo.[' + name + N'] WITH FULLSCAN ; '+ NCHAR(13) + NCHAR(10)
FROM sys.tables
FOR XML PATH(''),TYPE
).value('(./text())[1]','nvarchar(max)')
As a sidebar, I don't encounter the same problem on 2k5.
How much data did you test with? I have over 8300 tables to concatenate. On top of that the table names or fairly long, here's an "average" name.
dbo.[Groupe FORDIA Inc_ CONSO$Item Ledger Entry]
Another variable that might play into this. This db is case sensitive. Maybe this is the variable that tips the balance.
If you guys are willing to test on your system I'll post the actual table names so you can run on your systems.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply