Hello,
I'm trying to run the stored procedure found in this white paper from
Microsoft.
However, after correcting the stored procedure "add_column" which do not
compile at first, it seams that "get_indexstats" generates a wrong SQL Query
for execution :

the second query fails with "Msg 102, Level 15, State 1, Line 3 Incorrect
syntax near '-'."

select i2.database_id, i2.object_id, i2.index_id,
i2.partition_number , begintime=case min(i1.now) when max(i2.now) then
NULL else min(i1.now) end , endtime=max(i2.now) into #i
from indexstats i2 full outer join indexstats i1 on
i1.database_id = i2.database_id
and i1.object_id = i2.object_id
and i1.index_id = i2.index_id
and i1.partition_number = i2.partition_number
where i1.now >= 'Aug 21 2006 3:02:56:717PM' and i2.now = 'Aug 21 2006
3:05:49:773PM'
group by i2.database_id, i2.object_id, i2.index_id,
i2.partition_number

select i.database_id, db_name=db_name(i.database_id),
object=isnull(object_name(i.object_id),i.object_id ), indid=i.index_id,
part_no=i.partition_number ,[leaf inserts]=i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0) ,
[leaf deletes]=i2.leaf_delete_count - isnull(i1.leaf_delete_count,0)
,[leaf updates]=i2.leaf_update_count -
isnull(i1.leaf_update_count,0) ,[nonleaf inserts]=i2.nonleaf_insert_count -
isnull(i1.nonleaf_insert_count,0) ,[nonleaf
deletes]=i2.nonleaf_delete_count -
isnull(i1.nonleaf_delete_count,0) ,[nonleaf
updates]=i2.nonleaf_update_count -
isnull(i1.nonleaf_update_count,0) ,[nonleaf index
overhead]=(i2.nonleaf_insert_count -
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count -
isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count -
isnull(i1.nonleaf_update_count,0)) ,[leaf
allocations]=i2.leaf_allocation_count -
isnull(i1.leaf_allocation_count,0) ,[nonleaf
allocations]=i2.nonleaf_allocation_count -
isnull(i1.nonleaf_allocation_count,0)
,[allocations]=(i2.leaf_allocation_count -
isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count -
isnull(i1.nonleaf_allocation_count,0)) ,[leaf page
merges]=i2.leaf_page_merge_count -
isnull(i1.leaf_page_merge_count,0) ,[nonleaf page
merges]=i2.nonleaf_page_merge_count -
isnull(i1.nonleaf_page_merge_count,0) ,[range scans]=i2.range_scan_count -
isnull(i1.range_scan_count,0) ,[singleton
lookups]=i2.singleton_lookup_count -
isnull(i1.singleton_lookup_count,0) ,[index usage]=(i2.range_scan_count -
isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count -
isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0)) ,[row locks]=i2.row_lock_count -
isnull(i1.row_lock_count,0) ,[row lock waits]=i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0) ,[block %]=cast (100.0 *
(i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count -
isnull(i1.row_lock_count,0)) as numeric(5,2)) ,[row lock wait
ms]=i2.row_lock_wait_in_ms -
isnull(i1.row_lock_wait_in_ms,0) ,[avg row lock wait ms]=cast
((1.0*(i2.row_lock_wait_in_ms
- isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)) as numeric(20,1)) ,[page
locks]=i2.page_lock_count -
isnull(i1.page_lock_count,0) ,[page lock waits]=i2.page_lock_wait_count -
isnull(i1.page_lock_wait_count,0) ,[page lock wait
ms]=i2.page_lock_wait_in_ms -
isnull(i1.page_lock_wait_in_ms,0) ,[avg page lock wait ms]=cast
((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 +
i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as
numeric(20,1)) ,[index lock promotion
attempts]=i2.index_lock_promotion_attempt_count -
isnull(i1.index_lock_promotion_attempt_count,0) ,[index lock
promotions]=i2.index_lock_promotion_count -
isnull(i1.index_lock_promotion_count,0) ,[page latch
waits]=i2.page_latch_wait_count -
isnull(i1.page_latch_wait_count,0) ,[page latch wait
ms]=i2.page_latch_wait_in_ms -
isnull(i1.page_latch_wait_in_ms,0) ,[avg page latch wait ms]=cast
((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1
+ i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as
numeric(20,1)) ,[pageio latch waits]=i2.page_io_latch_wait_count -
isnull(i1.page_latch_wait_count,0) ,[pageio latch wait
ms]=i2.page_io_latch_wait_in_ms -
isnull(i1.page_latch_wait_in_ms,0) ,[avg pageio latch wait ms]=cast
((1.0*(i2.page_io_latch_wait_in_ms -
isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count -
isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1)) from #i i left
join indexstats i1 on i.begintime = i1.now and
i.database_id = i1.database_id and i.object_id = i1.object_id and
i.index_id = i1.index_id and i.partition_number = i1.partition_number left
join indexstats i2 on i.endtime = i2.now and
i.database_id = i2.database_id and i.object_id = i2.object_id and
i.index_id = i2.index_id and i.partition_number = i2.partition_number

If the author (or someone else) of this white paper has a version of the
procedure that works, I'm interrested.
TIA.

Hi Olivier

Any chance you could post a link to that white paper? I'd be interested in
having a look at it if there's a public url available.

Regards,
Greg Linwood
SQL Server MVP

"Olivier Matrot" <olivier.matrot.rte (AT) online (DOT) nospam> wrote

http://www.microsoft.com/technet/pro.../tsprfprb.mspx

"Greg Linwood" <g_linwood (AT) hotmail (DOT) com> wrote

Hi,

From my test, the stored procedure dbo.add_column failed to be executed;
also, I need to run the stored procedure dbo.init_index_operational_stats
before execute the dbo.get_indexstats; after those steps, I can execute the
stored procedure dbo.get_indexstats.

This is the stored procedure dbo.add_column after my correction:
create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0

while @add_stmt is not null and @EOS = 0
begin
--set @dbid=-1
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm
--@add_stmt
if @add_stmt is NULL
begin
select @add_stmt=@col_stmt
end

Please check whether or not your run dbo.init_index_operational_stats
before you execute dbo.get_indexstats.

Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Could you post your version of dbo.get_indexstats.
Mine is not working even after running dbo.init_index_operational_stats and
dbo.insert_indexstats.
TIA.

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Hi Olivier,
My dbo.get_indexstats is same as the article. Try recreating these stored
procedures again.
If this issue persists, please let me know, and I will send you mine
tommorrow.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

I've tried recreating the stored procedure with no luck.
TIA.

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Hi Olivier,
My steps are as following:
1. Create the sp_addcolumn stored procedure:
create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0

while @add_stmt is not null and @EOS = 0
begin -- I add a begin here
-- @dbid=-1, -- I comment this line due to no @dbid parameter
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm
--@add_stmt
if @add_stmt is NULL
select @add_stmt=@col_stmt
go

2. Create the stored procedure dbo.get_indexstats without modification.
3. Create the stored procedure dbo.init_index_operational_stats
4. Execute the stored procedure:
exec dbo.init_index_operational_stats
5. Execute the stored procedure dbo.get_indexstats.

You can try my steps and see whether or not the issue will occur.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Sincerely,
Charles Wang
Microsoft Online Community Support

You've ommited the procedure insert_indexstats that must be created and run
at least twice.
Give it a try, you'll see the error I'm talking about.
In your steps the table dbo.indexstats is empty. This is why
dbo.get_indexstats is working.


"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Hi Oliver,
After debugging the stored procedure with VS.NET, I find that this issue is
caused by the character '-' which is in other encoding not the default
ASCII '-', so SQL Server 2005 can't recognize it.
You can try copying the stored procedure to notepad, and replace all the
original characters with the default ASCII character.
This issue is annoyinng but luckily we got it at last :-)

Cheers,
Charles Wang
Microsoft Online Community Support