Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Index Management Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 5:16 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:38 PM
Points: 1,475, Visits: 1,640
Comments posted to this topic are about the item Index Management

Regards,
Sqlfrenzy

Post #803185
Posted Monday, October 19, 2009 10:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 8:15 AM
Points: 1, Visits: 23
technically very good document. please continue do more research on locks also.

Post #805425
Posted Tuesday, October 20, 2009 1:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 AM
Points: 7, Visits: 39
hi am kinda new here

can this be apllied on SQL 2000?

thanks!
Post #805492
Posted Tuesday, October 20, 2009 2:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:55 AM
Points: 22, Visits: 454
The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.

I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)
Post #805506
Posted Tuesday, October 20, 2009 2:49 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:38 PM
Points: 1,475, Visits: 1,640
darwin.maramot (10/20/2009)
hi am kinda new here

can this be apllied on SQL 2000?

thanks!


No..It doesn't applies to sql 2000


Regards,
Sqlfrenzy

Post #805511
Posted Tuesday, October 20, 2009 2:50 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:38 PM
Points: 1,475, Visits: 1,640
dagfinn.molde (10/20/2009)
The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.

I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)


thanks..I will review and update asap...


Regards,
Sqlfrenzy

Post #805513
Posted Tuesday, October 20, 2009 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 AM
Points: 7, Visits: 39
Sqlfrenzy (10/20/2009)
darwin.maramot (10/20/2009)
hi am kinda new here

can this be apllied on SQL 2000?

thanks!


No..It doesn't applies to sql 2000


ok thanks!
Post #805521
Posted Tuesday, October 20, 2009 3:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
nice article

"Keep Trying"
Post #805522
Posted Tuesday, October 20, 2009 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 20, 2009 3:08 PM
Points: 1, Visits: 9
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 ...."

Post #805578
Posted Tuesday, October 20, 2009 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 7:55 AM
Points: 3, Visits: 7
When trying to bring your functions to 'life', I changed the proc_FilltblIndexUsageInfo

ALTER Proc [dbo].[proc_FilltblIndexUsageInfo]
AS
Begin
Truncate table tblIndexUsageInfo
insert into tblIndexUsageInfo
select db_name(db_id()) DbName,
so.name as 'TableName',
ISNULL(si.name,'No Index') as IndexName,
si.index_id,
Case When is_primary_key=1 then 'Primary Key Constraint'
Else 'Index' End ConstraintType,
si.type_desc,
dbo.Uf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,
dbo.Uf_GetIndexCol(si.index_id,so.object_id,1) As IncludedCols,
spi.user_seeks,spi.user_scans,spi.user_lookups,spi.user_updates,
(user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage ',
dbo.Uf_GetIndexSize(si.index_id,so.object_id) 'IndexSizeKB',
Cast(
(user_seeks+user_scans+user_lookups+user_updates)/
dbo.Uf_GetIndexSize(si.index_id,so.object_id) As decimal(10,2))
As IndexUsagetoSizeRatio
from sys.objects so inner join sys.indexes si
on so.object_id=si.Object_id
inner join sys.dm_db_index_usage_stats spi
on spi.Object_id=so.Object_id
and si.index_id=spi.index_id
and spi.database_id=db_id()
where so.type='u'
END

Post #805626
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse