SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with query and index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hummer123
Starting Member

Cyprus
5 Posts

Posted - 04/02/2013 :  02:17:17  Show Profile  Reply with Quote
Hi,

I am trying to understand a issue we have with a large database and a query that we need to perform. I am not a database expert as such but know my way around a little ;-)

Server. 24 core CPU with 64GB memory and SAS drives.

The database has 2 tables.

Table 1 – domains_com contains a list of domains with approx. 160 million rows

Columns:

Domainid
Domainame
Locked
Ignored

This table already has an index and a query on any column is instant.

Table 2

This table contains multiple results of nameserver and whois data for the domains.

Columns

[domainid]
[registrar]
[whois_server]
[registrar_homepage]
[date_created]
[date_expiring]
[date_updated]
[nameservers]
[domain_status]
[domain_ip]
[temp_whois_result]
[last_checked]
[updated]
[app_name]
[crawl_ip]
[info_registrant]
[info_admin]
[info_tech]
[info_billing]

The problem I am having is with a queries where I need to retrieve the temp_whois_result column like below. This column can be quite large up 3k characters.

I have tried creating a non clustered index on results_com as follows and already have a index on domains_com.

Index Key Columns: domainid,domainame
Included columns: Last_Checked,App_Name,info_registrant, whois_server

declare @hours int set @hours = 3
SELECT TOP 200 domainame,Last_Checked,App_Name,info_registrant, whois_server,temp_whois_result
FROM domains_com_joined_results_com WHERE last_checked >= dateadd(hour, -1*@hours, getdate()) order by last_checked desc

When I use “Display estimated execution plan” for this query it tells me I should include the column temp_whois_result . The problem if I include this column is the index grows to over 80gb which I think is far too large.

If I don’t include the temp_whois_result in the index, then the query is instant.

The thing I don’t understand is that the where condition is on the “last_checked” column, so I thought that the query would find the records in the index with a pointer to the records in the table where the temp_whois_result column would be retrieved.

I have read quite a few articles on indexes, clustered v non clustered and am still not sure where I am going wrong.

Any help appreciated.

Thanks

Scott

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/02/2013 :  03:27:31  Show Profile  Visit chadmat's Homepage  Reply with Quote
If you don't include everything in the select list, then a seek on a NC index has to do a Bookmark Lookup to the clustered index (Which can be expensive if there are many results). This is why you are getting a suggestion to include the temp_whois_result column.

-Chad
Go to Top of Page

hummer123
Starting Member

Cyprus
5 Posts

Posted - 04/02/2013 :  04:12:36  Show Profile  Reply with Quote
Thanks, I think I understand this better now, I found this article which helped explain it well..

http://connectsql.blogspot.com/2011/01/sql-server-bookmark-lookups-query.html

So, is there anyway around this or better alternative ?
thanks

Scott
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/02/2013 :  05:40:21  Show Profile  Reply with Quote
I don't see a reason for index implementation. What you said is that "The problem I am having is with a queries where I need to retrieve the temp_whois_result column like below. This column can be quite large up 3k characters." Is it that the column being quite large upto 3k? I dont think its an issue, data can be in MBs and GBs ..
Is this query performing bad? I mean takes longer time to execute and bring back the desired data? If so .. is there any index implemented on last_checked column?

Cheers
MIK
Go to Top of Page

hummer123
Starting Member

Cyprus
5 Posts

Posted - 04/02/2013 :  06:51:38  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008



It maybe my lack of understanding of the SQL indexing. I will never perform a select query on the temp_whois_result, I just need to view the data. I was presuming that as the select is on the last_checked column then that is all I needed.

The actual size of the index is not really an issue, the problem is that the results table gets 250k inserts per day so the indexes get fragmented very quickly so when I do an index rebuild it takes much longer.

The index I have is below:

Index Key Columns: domainid,domainame
Included columns: Last_Checked,App_Name,info_registrant, whois_server

Thanks

Scott



Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/02/2013 :  08:11:25  Show Profile  Reply with Quote
Well my original question is still unanswered

Secondly, I don't think the specified index would be utlized by the query

SELECT TOP 200 domainame,Last_Checked,App_Name,info_registrant, whois_server,temp_whois_result
FROM domains_com_joined_results_com WHERE last_checked >= dateadd(hour, -1*@hours, getdate()) order by last_checked desc

Even if you include temp_whois_result column in the index.

And yes if you add/include more columns in an index it would have direct impact on the DML transactions.

Lastly, check for indexes that are not in-use, remove them at all in order to avoid unnecessary maintanance ... http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/06/15/find-unused-indexes-using-sysdm_db_index_usage_stats/

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000