April 17, 2015 at 11:08 am
declare @sql nvarchar(MAX)
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
quotename(s.name) + '.' + quotename(o.name) +
' WITH FULLSCAN; ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)
The below Dynamic TSQL throws Error:
Error:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FOR'.
USE master
GO
DECLARE @STR varchar(max), @sql nvarchar(MAX), @dbName nvarchar(max);
SET @dbName = 'user_db';
PRINT N'CHECKING DATABASE ' + @dbName;
SET @sql = 'USE ' + @dbname + ';' + '(SELECT '+'''UPDATE STATISTICS ''' + '+ ' + 'quotename(s.name)'+ '+' + '''.''' + '+' + 'quotename(o.name)' + '+' + '''WITH FULLSCAN; ''' + ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type ' +'= ' + '''U'' FOR XML PATH('' ''),TYPE).value(''.'''+ ','+ '''nvarchar(MAX)'''+')'
Print @sql
EXEC (@sql)
Not sure, why this is an error at FOR...
Any advise / suggestion?
Thanks.
April 17, 2015 at 11:27 am
Your nesting confused the heck out of me LOL, also afraid I can't help you troubleshoot xml, I'm pretty slow at that. Anyways...
-- is this what you're trying to do? Or are you trying to GENERATE the following code?
DECLARE @STR varchar(max), @sql nvarchar(MAX), @dbName nvarchar(max);
SET @dbName = 'user_db';
PRINT N'CHECKING DATABASE ' + @dbName;
SET @sql = 'USE ' + @dbname + ';' + (SELECT 'UPDATE STATISTICS ' + quotename(s.name) + '.' + quotename(o.name) + ' WITH FULLSCAN; ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U' FOR XML PATH(''),TYPE).value('.','nvarchar(MAX)')
select @sql
--EXEC (@sql)
-- if generating, I'd go something like this, you need a nested exec @sql
DECLARE @STR varchar(max), @sql nvarchar(MAX), @dbName nvarchar(max);
SET @dbName = 'user_db';
PRINT N'CHECKING DATABASE ' + @dbName;
SET @sql = 'DECLARE @sql nvarchar(MAX); USE ' + @dbname + '; SET @sql = (SELECT ''UPDATE STATISTICS '' + quotename(s.name) + ''.'' + quotename(o.name) + '' WITH FULLSCAN; '' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = ''U'' FOR XML PATH(''''),TYPE).value(''.'',''nvarchar(MAX)''); SELECT @sql; EXEC (@sql);'
select @sql
--EXEC (@sql)
It was really less work to just go ahead and rewrite the 2nd nesting level then troubleshoot, so sorry if that was less than helpful, also, I didn't actually run any statistics updates, but the generated thing looked ok. ALSO TEST EVERYTHING!
PS once I finally understood what you were doing, its an interesting idea, is it very practical for your situation?
April 19, 2015 at 1:35 am
Quick question, why not use sys.sp_updatestats?
😎
April 19, 2015 at 2:14 am
Eirikur Eiriksson (4/19/2015)
Quick question, why not use sys.sp_updatestats?😎
exactly what I put into his request for code review of updating stats using nested cursors posted on 7april. To DBA01, Instead of asking several different code review questions along similar lines, why not tell us exactly what you need doing and also tell us why ola hallingrens maintenance tool does not serve the purpose.
April 19, 2015 at 5:52 am
MadAdmin (4/19/2015)
Eirikur Eiriksson (4/19/2015)
Quick question, why not use sys.sp_updatestats?😎
exactly what I put into his request for code review of updating stats using nested cursors posted on 7april. To DBA01, Instead of asking several different code review questions along similar lines, why not tell us exactly what you need doing and also tell us why ola hallingrens maintenance tool does not serve the purpose.
Further on MadAdmin's advice and reiterating the question I've already asked:
Why not use Ola Hallengren's SQL Server Integrity Check rather than roll your own? Judging by the code you have posted, I cannot recommend anything but aborting the effort and use recognized solutions.
😎
April 19, 2015 at 6:40 am
Eirikur,
Can you suggest how to use sample percent and other hints using sp_updatestats??????????....???????
The table which has billions of records do u suggest running sp_updatestats???????????????????????????????????????
Thanks.
April 19, 2015 at 7:25 am
SQL-DBA-01 (4/19/2015)
Eirikur,Can you suggest how to use sample percent and other hints using sp_updatestats??????????....???????
The table which has billions of records do u suggest running sp_updatestats???????????????????????????????????????
Again reiterating the suggestion of using Ola Hallengren's SQL Server Index and Statistics Maintenance
😎
To answer your question, have a look at sys.dm_db_index_physical_stats and sys.dm_db_index_operational_stats.
USE [MY_DB_NAME];
GO
SET NOCOUNT ON;
SELECT
IPS.database_id
,IPS.object_id
,IPS.index_id
,IPS.partition_number
,IPS.index_type_desc
,IPS.alloc_unit_type_desc
,IPS.index_depth
,IPS.index_level
,IPS.avg_fragmentation_in_percent
,IPS.fragment_count
,IPS.avg_fragment_size_in_pages
,IPS.page_count
,IPS.avg_page_space_used_in_percent
,IPS.record_count
,IPS.ghost_record_count
,IPS.version_ghost_record_count
,IPS.min_record_size_in_bytes
,IPS.max_record_size_in_bytes
,IPS.avg_record_size_in_bytes
,IPS.forwarded_record_count
,IPS.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'SCHEMA_NAME.TABLE_NAME'),1,NULL,NULL) IPS;
SELECT
IOS.database_id
,IOS.object_id
,IOS.index_id
,IOS.partition_number
,IOS.leaf_insert_count
,IOS.leaf_delete_count
,IOS.leaf_update_count
,IOS.leaf_ghost_count
,IOS.nonleaf_insert_count
,IOS.nonleaf_delete_count
,IOS.nonleaf_update_count
,IOS.leaf_allocation_count
,IOS.nonleaf_allocation_count
,IOS.leaf_page_merge_count
,IOS.nonleaf_page_merge_count
,IOS.range_scan_count
,IOS.singleton_lookup_count
,IOS.forwarded_fetch_count
,IOS.lob_fetch_in_pages
,IOS.lob_fetch_in_bytes
,IOS.lob_orphan_create_count
,IOS.lob_orphan_insert_count
,IOS.row_overflow_fetch_in_pages
,IOS.row_overflow_fetch_in_bytes
,IOS.column_value_push_off_row_count
,IOS.column_value_pull_in_row_count
,IOS.row_lock_count
,IOS.row_lock_wait_count
,IOS.row_lock_wait_in_ms
,IOS.page_lock_count
,IOS.page_lock_wait_count
,IOS.page_lock_wait_in_ms
,IOS.index_lock_promotion_attempt_count
,IOS.index_lock_promotion_count
,IOS.page_latch_wait_count
,IOS.page_latch_wait_in_ms
,IOS.page_io_latch_wait_count
,IOS.page_io_latch_wait_in_ms
,IOS.tree_page_latch_wait_count
,IOS.tree_page_latch_wait_in_ms
,IOS.tree_page_io_latch_wait_count
,IOS.tree_page_io_latch_wait_in_ms
,IOS.page_compression_attempt_count
,IOS.page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(N'SCHEMA_NAME.TABLE_NAME'),1,NULL) IOS;
April 19, 2015 at 11:46 am
Eirikur Eiriksson (4/19/2015)
SQL-DBA-01 (4/19/2015)
Eirikur,Can you suggest how to use sample percent and other hints using sp_updatestats??????????....???????
The table which has billions of records do u suggest running sp_updatestats???????????????????????????????????????
Again reiterating the suggestion of using Ola Hallengren's SQL Server Index and Statistics Maintenance
To answer your question, have a look at sys.dm_db_index_physical_stats and sys.dm_db_index_operational_stats.
I went to Ola's site to see how to update statistics with sampling just to see if DBA1 is having issues finding it and it is very easy to find which parameter to provide.
DBA1, if you want to see how to fix your code, at least get the Ola Hallengren index optimise script and see how he did what you want.
I would say that it is the text book solution to any of your questions, compatible from 2005 all the way to 2014 (he is very thorough) and is freely available in the proc suggested at the location in Eirikur's post.
Here is Brent Ozar giving a lesson on how to use Ola's script.
http://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/
Brad McGehee's post on Ola's script.
I seriously doubt if the solution can be improved on, unless this is just a fact finding academic learning exercise.
As an aside, I think Ola should open a Patreon account because all of us are using his code.
Surely a few of us will happily put 10 Pound into an account for him, considering how much easier he has made life.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply