February 10, 2016 at 6:36 am
Hi All,
I have total 460 million row in the table. I am running query for year 2016 and month = 7 and we have total 8 millions records. query is taking 20 min to complete after creating index. estimated plan show index seek. Is there any possibility to make my query faster ?
any suggestion...
select *
from dbo.ttfgld482100 with (nolock)
where t_fyer = 2016 and t_fprd = 7
February 10, 2016 at 6:40 am
patla4u (2/10/2016)
Hi All,I have total 460 million row in the table. I am running query for year 2016 and month = 7 and we have total 8 millions records. query is taking 20 min to complete after creating index. estimated plan show index seek. Is there any possibility to make my query faster ?
any suggestion...
select *
from dbo.ttfgld482100 with (nolock)
where t_fyer = 2016 and t_fprd = 7
Don't use SELECT *. Select only the columns that you need.
Please post the Actual Execution Plan.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 10, 2016 at 6:51 am
OK .. Thanks. Let me try and let you know.
Thanks
February 10, 2016 at 7:10 am
You should also be aware that the NOLOCK hint can lead to some nasty issues, namely:
1) The possibility that 'ghost' data (data which is inserted as part of a transaction and then rolled back) is returned. Even though this data never made it to the database, as it was never committed, it would still be returned by a SELECT with (NOLOCK).
2) The possibility that the same data will be returned more than once.
If a selected row is updated while the SELECT with (NOLOCK) is running, both versions may be returned.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 10, 2016 at 7:23 am
patla4u (2/10/2016)
Hi All,I have total 460 million row in the table. I am running query for year 2016 and month = 7 and we have total 8 millions records. query is taking 20 min to complete after creating index. estimated plan show index seek. Is there any possibility to make my query faster ?
any suggestion...
select *
from dbo.ttfgld482100 with (nolock)
where t_fyer = 2016 and t_fprd = 7
Without further information:
Yes - get more memory and faster IO. You are right around the breakpoint between doing index seek/bookmark lookup and a table scan (1.7% of the table actual). Bouncing those rotating disks in random IO for the seek/lookup plan is CRUSHINGLY bad for performance.
Use sp_whoisactive to check for blocking and waits (I expect IO related waits to dominate).
Do a file IO stall differential analysis while the query is running. sp_whoisactive has ability to do this. review Adam Machanic's 30-day blog post series on the sproc on sqlblog.com
Do a wait stats differential analysis while the query is running.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply