October 20, 2009 at 2:54 pm
In some cases, the columns to be indexed disregarded the column names. For exampe, I have columns named with an underscore....these were parsed as separate column names. And, the columns in the new index sometimes have a preceding , ...Example:
Create Index Ix_OUTBOUND_999_XXX_Date_Sent ON [DBNAME].[dbo].[OUTBOUND_999_XXX](,XXX,Date,Sent)Include (ID, Batch_Name_XX)
Should be:
Create Index Ix_OUTBOUND_999_XXX_Date_Sent ON [DBNAME].[dbo].[OUTBOUND_999_XXX](XXX_Date_Sent) Include (ID, Batch_Name_XX)
October 20, 2009 at 3:02 pm
pnewhart (10/20/2009)
In some cases, the columns to be indexed disregarded the column names. For exampe, I have columns named with an underscore....these were parsed as separate column names. And, the columns in the new index sometimes have a preceding , ...Example:Create Index Ix_OUTBOUND_999_XXX_Date_Sent ON [DBNAME].[dbo].[OUTBOUND_999_XXX](,XXX,Date,Sent)Include (ID, Batch_Name_XX)
Should be:
Create Index Ix_OUTBOUND_999_XXX_Date_Sent ON [DBNAME].[dbo].[OUTBOUND_999_XXX](XXX_Date_Sent) Include (ID, Batch_Name_XX)
I will check and update....thanks...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 3:03 pm
einman33 (10/20/2009)
Nice content author, keep up the good work.Would like to bring up a dissenting opinion though. I struggle with the auto creation of indexes. I have used the missing index dmv many times, and I think it is an excellent choice for pointing you in the direction of where there is an index problem. But I do not agree with the automation of the index creation.
Some thoughts:
-Huge risk of over indexing the entity, which can really hurt dml
-Still need manual intervention to determine if the table is clustered correctly, as the suggested index
may in fact need to be the sort order of the table.
- In my experience the index that you would create would be really really wide. This is probably a design flaw with the table to begin with
Am I off base here, are we at the point where literally SQL can index itself? Again, I feel this view should only be used as a suggestion of where there are index problems, not the seed for the index bot.
thanks...you are right ... though I am not insisting on creating each and every missing index...with my experience I can say that an index with significance factor close 80000 are worth considering. you can analyse and form a baseline for your system
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 7:07 pm
==========================================================
Code posted is very inconsistent.
Uppercase, lowercase, schema names missing.
Prefixes are inconsistent - sometimes "Uf_", othertimes "fn_"
select * is naughty unless you need the full result set...
When using the "EXISTS" keyword you can simply "SELECT 1 WHERE ...."
==========================================================
"select * " isn't harmful only when using the "EXISTS() " keyword.
October 21, 2009 at 4:48 am
Hi ;
On Rebuilding Indexes on Stored Procedure proc_RebuildSelectedIndexes
on this line: SELECT IDENTITY(int,1,1) AS Sno
I get the following error: Parameter 3 is incorrect for this statement.
and sometimes : Invalid object name '#temp_2' which is wierd because its created there on the fly.
The othe modules are now working, what is left is the Rebuilding of Indexes which is crucial.
October 21, 2009 at 7:31 am
Hi,
Good useful article once the errors are sorted.
I myself have left the rebuilding of indexes out as I always prefer to manually check any index creation before going ahead no matter how useful the views think an index may be.
What I have done is built this into an SSIS which goes across all my servers and reports what it finds back to my central Admin DB... all this data sits quite comfortably now within SSRS.
Thanks very much... already coming in very handy.
October 21, 2009 at 7:46 am
can the article be reposted with all the corrections? it will be easier for anyone in the future...........
October 21, 2009 at 8:27 am
Another tip:
Create a separate schema for those procedures, like admin. instead of dbo.
And let the schema be a parameter to those procedures.
This prevents your own tables ( or any other temp or admin tables ) to appear on the final result.
I would appreciate if you could correct the code and edit your initial Article with it.
October 21, 2009 at 9:13 am
the issue is here:
FROM sys.dm_db_index_physical_stats(@dbid, @objectid,@index_id, NULL , 'Limited')
Parameter @index_id is really a nightmare. I am just trying to check on the parameters sent by the Management Function sys.dm_db_index_physical_stats
October 21, 2009 at 9:44 am
thanks for the good response.....
I will review the code and will update by this weekend....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 22, 2009 at 2:10 pm
In Step 1 code block three is:
Create Function Uf_GetIndexSize
(@index_id int,@tableid int)
Returns float
AS
BEGIN
return (select cast(reserved as float*8192)/(1024) from sysindexes
where indid=@index_id and id=@tableid)
End
I get:
Msg 102, Level 15, State 1, Procedure Uf_GetIndexSize, Line 6
Incorrect syntax near '*'.
I found that it moving the ")" so the code looks like "float)*8192/" it works.
Is this the same thing?
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
October 22, 2009 at 2:22 pm
In step 1 the fourth code block I believe you omitted the "s" in the FROM line for the ALIAS "so"
I changed the code to "from sys.objects so inner join sys.indexes si
on so.object_id=si.Object_id" and it compiled.
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
October 22, 2009 at 11:55 pm
Wchaster; please just do this:
(select cast(reserved as float)*8192/(1024) move 8192 & the * sign out of the bracket. It will work withoiut changing precedence because the signs are of the same precedence.
October 23, 2009 at 11:57 am
That's the ticket! Thanks for the add.
October 24, 2009 at 2:47 am
I did manage to play around the stored proc - proc_RebuildSelectedIndexes.
The first 2 module should not be altered because there are perfect.
I crated my own Table
CREATE TABLE [dbo].[tblIndexFrag](
[SNO] [bigint] IDENTITY(1,1) NOT NULL,
[SCHEMA_NAME] [varchar](20) NULL,
[TABLE_NAME] [varchar](50) NULL,
[INDEX_NAME] [varchar](50) NULL,
[OBJECT_ID] [bigint] NULL,
[OBJECT_TYPE] [varchar](30) NULL,
[INDEX_USAGE] [bigint] NULL,
[INDEX_USAGE_TO_SIZE_RATIO] [numeric](8, 2) NULL,
[AVG_FRAG] [numeric](8, 4) NULL)
and then played around with the Proc :
ALTER PROC proc_RebuildSelectedIndexes
AS
declare @SchemaName varchar(20),
@TableName varchar(50),
@IndexName varchar(50),
@objectId bigint;
declare @avg_Frag numeric,
@type_desc varchar(30) ,
@indexUsage numeric ,
@indexUsageTosizeRatio numeric ;
DECLARE @COUNTER INT;
DECLARE @Qry VARCHAR(1000),
@IxOpr VARCHAR(1000);
TRUNCATE TABLE tblIndexFrag
declare curFixIndex CURSOR
FOR
SELECT DISTINCT s.name AS SchemaName ,o.name AS TableName,
i.name AS IndexName,o.object_id, i.type_desc
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN tblMostUsedIndexes u
ON i.name = u.IndexName
AND o.name = u.TableName
AND s.Name = u.SchemaName
AND o.type = 'U' AND i.name IS NOT NULL
AND u.indexUsage > 0
order by i.name
OPEN curFixIndex
FETCH NEXT FROM curFixIndex
INTO @SchemaName,@TableName,@IndexName,@objectId,@type_desc --,@indexUsageTosizeRatio,@indexUsage
WHILE @@FETCH_STATUS = 0
BEGIN
SET @type_desc = @type_desc + ' INDEX'
SELECT @avg_Frag = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(db_id(),
@objectId, NULL, NULL , 'Limited')
WHERE index_type_desc = rtrim(@type_desc)
INSERT INTO tblIndexFrag ([SCHEMA_NAME], TABLE_NAME,INDEX_NAME,OBJECT_ID,OBJECT_TYPE,AVG_FRAG)--,INDEX_USAGE INDEX_USAGE_TO_SIZE_RATIO,)
VALUES(@SchemaName,@TableName,@IndexName,@objectId,@type_desc,@avg_Frag)--,@indexUsage @indexUsageTosizeRatio,)
FETCH NEXT FROM curFixIndex
INTO @SchemaName,@TableName,@IndexName,@objectId,@type_desc
,@indexUsageTosizeRatio ,@indexUsage
END;
CLOSE curFixIndex
DEALLOCATE curFixIndex
--Reindexing Now
DECLARE curDefrag CURSOR
FOR SELECT SCHEMA_NAME,TABLE_NAME,INDEX_NAME,AVG_FRAG FROM tblIndexFrag WHERE avg_frag > 0
OPEN curDefrag
FETCH NEXT FROM curDefrag INTO @SchemaName, @TableName,@IndexName,@avg_frag
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@avg_frag<=20)
BEGIN
SET @Qry ='Alter index ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName + ' REORGANIZE;'
SET @IxOpr ='REORGANIZE'
END
IF(@avg_frag>20)
BEGIN
SET @Qry ='Alter index ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName + ' REBUILD;'
SET @IxOpr ='REBUILD'
END
PRINT @qry
EXECUTE(@qry)
FETCH NEXT FROM curDefrag INTO @SchemaName, @TableName,@IndexName,@avg_frag
END
CLOSE curDefrag
DEALLOCATE curDefrag
Its working to the effect that I want.
Please feel free to check and help enhancing it for performance issues..........
Here: sys.dm_db_index_physical_stats(@dbid, @objectid, @index_id, NULL , 'Limited') we cannot pass the @index_id parameter, its wrong. Try to pass NULL........ Suggestions?
Peter, we are waiting for you because the brilliant idea is yours!
Viewing 15 posts - 31 through 45 (of 84 total)
You must be logged in to reply to this topic. Login to reply