Index guidance needed

Question

Index guidance needed

Viewing 4 posts - 16 through 18 (of 18 total)

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

Y.B. SSChampion Points: 11649 More actions · Answer 1

CferMN (10/4/2016)
GilaMonster (9/15/2016)
What's the table frequently filtered by?
GilaMonster (9/16/2016)
Good start
What's the most common predicate used against that table?
I'm new to index/performance tuning. Could you please explain how to tell what the table is most frequently filtered by or how to tell the most common predicate used against the table? Is that found in a particular DMV or is that just knowledge of your tables and how they are being queried?

Look at the 'WHERE' clause in the most common queries that are run by the application/users. Try to find out which columns are being most often used to filter the data. This is a good starting point in trying to figure out what your indexes should look like.

SELECT quote FROM brain WHERE original = 1
0 rows returned

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

MickyD (9/15/2016)
Hi everyone.
I recently came across a dev database in our organization that had no clustered indexes and circa 100 million row.....:crazy:
In order to rectify I need some advice on where to create the clustered index. The table seems to be queried very often , but nothing seems to be removed.
Table tracks network activity and is designed as below.
Col
[SrcIP] - varchar(25)
[SrcDNS] - varchar(MAX)
[DstIP] - varchar(25)
[DstDNS] - varchar(MAX)
[DstPort] - varchar(MAX)
[Protocol] - varchar(MAX)
[Date] - datetime
[ConversationCount] - int
I am keen to learn how to improve the performance of this table and queries that are run against it.
What should I be looking at in terms of creating a clustered index , and where should it be created ?
Any advice on improvements gratefully received.
thanks everyone

Since this is a dev environment and there are no indexes, you really cant go too wrong here with experimenting a common sense approach. Make sure you clear with your team before adding a index to a huge heap to ensure no one is using it at the moment you modify (if this area is shared).

I figure the values in source and destination IPs can overlap, so the date field is worth considering IMHO. Better yet, right click the table and select 'object dependencies' to views objects that depend on the table. Examine the definition of stored procedures and view that result. Look at the predicates in these objcets...

For example if storedProcA has something like

WHERE DATE >= @fromDate and Date < @toDate

then you know the [DATE] field is a worthy candidate for a clustered index.

My guess is that more than likely for network traffic data, seeing occurrence at a point in time (a one time query) would be less common than aggregating data in some fashion. I am certain the date field will be part of the mix.

EDIT - the code portion should display

WHERE DATE >= @fromDate and Date < @toDate

----------------------------------------------------

Sergiy SSC Guru Points: 110208 More actions · Answer 3

MickyD (9/15/2016)
Hi everyone.
I recently came across a dev database in our organization that had no clustered indexes and circa 100 million row.....:crazy:
In order to rectify I need some advice on where to create the clustered index. The table seems to be queried very often , but nothing seems to be removed.
Table tracks network activity and is designed as below.
Col
[SrcIP] - varchar(25)
[SrcDNS] - varchar(MAX)
[DstIP] - varchar(25)
[DstDNS] - varchar(MAX)
[DstPort] - varchar(MAX)
[Protocol] - varchar(MAX)
[Date] - datetime
[ConversationCount] - int
I am keen to learn how to improve the performance of this table and queries that are run against it.
What should I be looking at in terms of creating a clustered index , and where should it be created ?
Any advice on improvements gratefully received.
thanks everyone

Pretty much shooting in the dark here, but there is a strong noise coming fro that direction, so it must hit something:

CREATE CLUSTERED INDEX ON TableName([Date])

And I join everyone about the data types.

Consider VARCHAR(MAX) forbidden data type in tables.

For IPv4 addresses (IP and DNS) use BINARY(4), for port numbers - int.

Protocol - find the longest string inserted so far and double its length. But better find the reference paper about the protocols you're using and adjust the column length accordingly.

_____________
Code for TallyGenerator

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 4

thanks everyone , appreciate the advise and will be looking into the suggestions provided.