Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Should I hunt this person down?

Author  Topic 

PBoy
Starting Member

22 Posts

Posted - 2014-11-19 : 18:27:04
Hi,

I have just join a new company and I have been looking at there database to get a feel for what is going on. I'm not a DBA but I know my ways round sql server but I came across this type of set-up and was wondering should I hunt the person down and kill them for this sort off rubbish? this is on a few tables

the trigger are quiet heavy in code and the indexes are indexing value that are always changing... I feel this is bad what you think?



Cheers
P

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 19:52:05
Why would an index that is always changing be bad? Though I'm not a huge fan of triggers, I don't see an issue with them as long as they are efficient and don't add too much time to the transactions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

PBoy
Starting Member

22 Posts

Posted - 2014-11-20 : 03:55:11
quote:
Originally posted by tkizer

Why would an index that is always changing be bad? Though I'm not a huge fan of triggers, I don't see an issue with them as long as they are efficient and don't add too much time to the transactions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Hi Tara,

I just found it weird that some one would index on amount fields etc and from what I can see there is no query or reports that are looking to use the indexes created so I cant really see any value in having then or having the one shown...

The triggers look to be updating things that are not relevant may have been in 2006 but not now seems the triggers have been added to for other stuff but not really cleaned.

Seems to me indexes have been created to maybe make one query run alittle faster, when I asked about other ones I got the answer its was to make our workflow work faster ..... so did it?..... no .... why have the index then.

Cheers
P
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-20 : 11:27:30
You can run this query to determine if an index isn't being used. Please note that the results are affected by the last time SQL was restarted. Never drop an index based on this report unless you know you've run through a whole cycle since the last restart. For instance, if you have a monthly job that runs and might need an index, make sure that job has run before determining to drop an index. Learn from my mistake!

*Query taken from http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx

SELECT * FROM (
SELECT
o.name
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
--inner join sys.filegroups f on i.data_space_id = f.data_space_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0) t
WHERE reads = 0


You can run this query to determine which queries are using a particular index. This only works if the query is in cache. If the cache has been dropped, you won't get good results.

*Query is from Jonathan Kehayias from SQLskills

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = 'index name goes here';

-- Make sure the name passed is appropriately quoted
IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName);
--Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName;
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';

-- Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 11:51:07
quote:
Originally posted by PBoy

Hi,

I have just join a new company and I have been looking at there database to get a feel for what is going on. I'm not a DBA but I know my ways round sql server but I came across this type of set-up and was wondering should I hunt the person down and kill them for this sort off rubbish? this is on a few tables

the trigger are quiet heavy in code and the indexes are indexing value that are always changing... I feel this is bad what you think?



Cheers
P



I wouldn't advise you to "hunt the person down and kill them". Even assuming that you don't plan (I hope) on literally killing them, you may put your new job at risk.
Go to Top of Page
   

- Advertisement -