Should I add indexes to a log table?

Question

Should I add indexes to a log table?

Viewing 14 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

5000 inserts a day is a pretty light load, if your reports are having performance issues then yes adding indexes is a good idea. Unless you have some kind of massive space issues or some super fast insert time requirements i can't imagine why there would be a problem adding some indexes for your reports, they're a pretty basic feature of SQL Server.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Before you add any indexes, examine the code that's having the performance issue and make sure that the code is SARGable and will actually be able to make use of indexes. That also means checking for datatype mismatches where an entire column must be scanned to be evaluated, which would override the use of an index as a seek and, at best, would result in a scan of the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Something tells me that you would benefit more by having the clustered index on StartDateTime. I don't see how or why ExecutionID can be relevant to your queries.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

river1 SSC-Insane Points: 24315 More actions · Answer 4

Jeff Moden - Wednesday, January 24, 2018 12:48 PM
Before you add any indexes, examine the code that's having the performance issue and make sure that the code is SARGable and will actually be able to make use of indexes. That also means checking for datatype mismatches where an entire column must be scanned to be evaluated, which would override the use of an index as a seek and, at best, would result in a scan of the index.

Hello, what do you mean by sargable ? How can a datatype cause that? Can you please help me understand?

Thank you

river1 SSC-Insane Points: 24315 More actions · Answer 5

Luis Cazares - Wednesday, January 24, 2018 1:03 PM
Something tells me that you would benefit more by having the clustered index on StartDateTime. I don't see how or why ExecutionID can be relevant to your queries.

I will post the code here as it is not a long one so that I can here from you

river1 SSC-Insane Points: 24315 More actions · Answer 6

One thing seems that we all agree. As this is purely a delete and insert table,data compression at a page level will be helpful as it may help in terms of i/o, correct?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

river1 - Wednesday, January 24, 2018 1:04 PM
Jeff Moden - Wednesday, January 24, 2018 12:48 PM
Before you add any indexes, examine the code that's having the performance issue and make sure that the code is SARGable and will actually be able to make use of indexes. That also means checking for datatype mismatches where an entire column must be scanned to be evaluated, which would override the use of an index as a seek and, at best, would result in a scan of the index.
Hello, what do you mean by sargable ? How can a datatype cause that? Can you please help me understand?
Thank you

There's plenty of information about that topic, you just need to Google it. Here's one example https://www.sqlinthewild.co.za/index.php/2016/09/13/what-is-a-sargable-predicate/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

river1 - Wednesday, January 24, 2018 1:11 PM
One thing seems that we all agree. As this is purely a delete and insert table,data compression at a page level will be helpful as it may help in terms of i/o, correct?

It will help on terms of I/O but will affect on terms of CPU. Your benefit will depend on your resources.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

river1 - Wednesday, January 24, 2018 1:11 PM
One thing seems that we all agree. As this is purely a delete and insert table,data compression at a page level will be helpful as it may help in terms of i/o, correct?

Heh... hold the phone.... ONLY insert and delete? No SELECTs????

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24315 More actions · Answer 10

Jeff Moden - Wednesday, January 24, 2018 2:42 PM
river1 - Wednesday, January 24, 2018 1:11 PM
One thing seems that we all agree. As this is purely a delete and insert table,data compression at a page level will be helpful as it may help in terms of i/o, correct?
Heh... hold the phone.... ONLY insert and delete? No SELECTs????

Select is Only done by this ssrs report once per day

river1 SSC-Insane Points: 24315 More actions · Answer 11

Luis Cazares - Wednesday, January 24, 2018 1:19 PM
river1 - Wednesday, January 24, 2018 1:11 PM
One thing seems that we all agree. As this is purely a delete and insert table,data compression at a page level will be helpful as it may help in terms of i/o, correct?
It will help on terms of I/O but will affect on terms of CPU. Your benefit will depend on your resources.

If it only as inserts and deletes i dont think it can decrease cpu performance. At least this is what i read in some posts

river1 SSC-Insane Points: 24315 More actions · Answer 12

Luis Cazares - Wednesday, January 24, 2018 1:14 PM
river1 - Wednesday, January 24, 2018 1:04 PM
Jeff Moden - Wednesday, January 24, 2018 12:48 PM
Before you add any indexes, examine the code that's having the performance issue and make sure that the code is SARGable and will actually be able to make use of indexes. That also means checking for datatype mismatches where an entire column must be scanned to be evaluated, which would override the use of an index as a seek and, at best, would result in a scan of the index.
Hello, what do you mean by sargable ? How can a datatype cause that? Can you please help me understand?
Thank you
There's plenty of information about that topic, you just need to Google it. Here's one example https://www.sqlinthewild.co.za/index.php/2016/09/13/what-is-a-sargable-predicate/

After checking your link, it seems that my code is nor SARGable:

EXEC ( '

SELECT

DISTINCT (REPLACE(REPLACE(SchemaName, ''['', ''''), '']'', '''') + '' - '' + REPLACE(REPLACE(spname, ''['', ''''), '']'', '''')) SPNAME

FROM

' + @pDB + '.[Logging].[SPExecutions]

where

convert( date, startdatetime,103) >= convert( date, ''' + @StartDate + ''' , 103)
AND
convert (date, enddatetime,103) <= convert (date, ''' + @EndDate + ''', 103)

ORDER BY

(REPLACE(REPLACE(SchemaName, ''['', ''''), '']'', '''') + '' - '' + REPLACE(REPLACE(spname, ''['', ''''), '']'', ''''))

' )

because it has a convert function compering the dates, correct?

river1 SSC-Insane Points: 24315 More actions · Answer 13

Now code is without converts. like:

EXEC ( '

SELECT

DISTINCT (REPLACE(REPLACE(SchemaName, ''['', ''''), '']'', '''') + '' - '' + REPLACE(REPLACE(spname, ''['', ''''), '']'', '''')) SPNAME

FROM

' + @pDB + '.[Logging].[SPExecutions]

where

startdatetime >= convert( date, ''' + @StartDate + ''' , 103)
AND
enddatetime <= convert (date, ''' + @EndDate + ''', 103)

ORDER BY

(REPLACE(REPLACE(SchemaName, ''['', ''''), '']'', '''') + '' - '' + REPLACE(REPLACE(spname, ''['', ''''), '']'', ''''))

' )

So it is SARGable. I think 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

Maybe this would make the code easier to read and prevent any SQL injection.
DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'SELECT DISTINCT PARSENAME(SchemaName, 1) + '' - '' + PARSENAME(spname, 1) SPNAME FROM ' + QUOTENAME(name) + N'.[Logging].[SPExecutions] WHERE startdatetime >= @StartDate AND enddatetime <= @EndDate ORDER BY SPNAME;' FROM sys.databases WHERE name = PARSENAME( @pDB, 1);

EXECUTE sp_executesql @SQL, N'@StartDate date, @EndDate date', @StartDate, @EndDate;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2