November 2, 2007 at 3:02 pm
We aren't migrating till early next year. Hopefully some of you can recall your 2k experience and maybe help me out? If not, if you could point me to a forum where someone might be able to help, I'd sure appreciate it.
I'm not sure what is happening but here's the scenario.
I have a stored proc:
SELECT
master_ref_lead_id,
submit_dateTime,
First_name,
Last_name,
CASE WHEN (CONVERT(varchar(10), home_phone) = ' ')
THEN ' ' ELSE '1(' + substring(dl.home_phone, 1, 3) + ')' + substring(dl.home_phone, 4, 3) + '-' + substring(dl.home_phone, 7, 4) END AS home_phone,
CASE WHEN (CONVERT(varchar(10), work_phone) = ' ') THEN ' ' ELSE '1(' + substring(dl.work_phone, 1, 3) + ')' + substring(dl.work_phone, 4, 3)
+ '-' + substring(dl.work_phone, 7, 4) END AS work_phone,
s.state_code AS state,
email,
zip,
src_id,
qc_notes,
FROM dbo.preLeads dl LEFT OUTER JOIN
dbo.states s ON dl.state_id = s.state_id
WHERE status_id = 0 and processed = 0 and home_phone <> '' and first_name <> '' and ((passed_real_time_qc in (0,1)) OR (passed_real_time_qc is NULL))
and master_ref_lead_id not in (Select master_ref_lead_id from blockedpreleads bpl where bpl.master_ref_lead_id = dl.master_ref_lead_id)
order by master_ref_lead_id asc
I believe the indexes are good but I could be mistaken here. When I run the proc, (or my users come in and use it first thing in the morning) the proc takes ~ 3 mins to run. After the first run up until everyone goes home for the day, the query is instant. It's against ~ 1million records and the blocked preleads table contains around 10K records. The result set is typically 500-700 in the morning.
During the 3 mins or so of the first run, the hard drive is pegged and the database is unresponsive until the query finishes. The rest of the day the hard drive hits are minimal and the DB runs perfectly fine. Happens every day without fail. I've tried using with recompile and not using it to no effect. Any ideas or suggestions on why this is happening and maybe tips on getting it to stop?
Here are the current indexes:
CREATE INDEX [IDX_MAX] ON [dbo].[preLeads]([master_ref_lead_id], [home_phone], [submit_dateTime]) ON [PRIMARY]
GO
CREATE INDEX [IDX_Profiler] ON [dbo].[preLeads](, [master_ref_lead_id], [submit_dateTime]) ON [PRIMARY]
GO
CREATE INDEX [IDX_Profile1] ON [dbo].[preLeads]([submit_dateTime], [master_ref_lead_id], [home_phone]) ON [PRIMARY]
GO
CREATE INDEX [IDX_Profile2] ON [dbo].[preLeads]([status_ID], [processed], [home_phone], [master_ref_lead_id]) ON [PRIMARY]
GO
CREATE INDEX [IDX_Profiler3] ON [dbo].[preLeads]([status_ID], [submit_dateTime], [master_ref_lead_id], [affiliate_ID], [src_ID]) ON [PRIMARY]
GO
CREATE INDEX [IDX_IOFix] ON [dbo].[preLeads]([status_ID], [processed], [home_phone], [first_name]) ON [PRIMARY]
GO
CREATE INDEX [IDX_IOFix2] ON [dbo].[preLeads]([src_ID], [Referer], [submit_dateTime], [master_ref_lead_id]) ON [PRIMARY]
GO
CREATE INDEX [IDX_TuneAug] ON [dbo].[preLeads]([submit_dateTime], ) ON [PRIMARY]
GO
CREATE INDEX [IDX_QueueFix] ON [dbo].[preLeads]([first_name] DESC , [home_phone] DESC , [status_ID] DESC , [processed] DESC , [passed_real_time_qc] DESC ) ON [PRIMARY]
GO
November 8, 2007 at 1:52 am
What you describes sounds typically as a 'cold cache'.
The first time, no data is in the data cache, so it has to be read from disk.
After this first time, there are no more issues, since the data needed is already in cache.
Question is why the cache gets cold overnight?
Seems you have some activity that flushes the datacache each night.
Server reboot?
Large batches?
Some other activity that will age the data out of cache, resulting in physical reads the next morning?
It also seems that your diskdrives gets overloaded from the initial query.
What's the spec for the drive-system om this box?
Perhaps even the cpu?
Is the server spec up to the task to serve as well as it should?
/Kenneth
November 8, 2007 at 8:20 am
The cache problem you mention would make sense as we have several very large batch jobs that run overnight. Is there a way to force the db to not dump a particular set of cached data?
November 8, 2007 at 8:25 am
It might be easier to "recache" the data AFTER the big maintenance is over. So - let the server do what it needs with all of the RAM it needs, then run something that would get this data back into the cache once maintenance is over.
In other words - run one of these "for free" before anyone shows up....from the server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 8:42 am
Yes, what Matt is suggesting would probably do the trick.
Perhaps set up a job that runs this query once in the morning a half hour or so before office opens.
Then the cache would be 'seeded' and you're all set to go from the start.
/Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply