ChrisM@Work (2/26/2014)

---

It sounds as if this is in the design phase - if this is the case, then consider pivoting the table you display here as your starting point, so...

put a semicolon in front of the with on the CTE

We do this all the times for finding the nearest store from your current location. Yes, the big long formula works, is accurate down to inches, and is feasible on...

I have used caching more for search pages, but not with more recent hardware. The old days of submitting a request and waiting 15 minutes are gone. We would save...

please post the times from the script I posted.

you do not have any method to force SQL to keep data in memory. It uses a LRU (Least Recently Used) algorithm...

Grant Fritchey (2/26/2014)

---

The only part of that statement I would disagree with is outputting trace data to a table. I wouldn't recommend that. I would output it to a file...

default schema. when you added sysadmin it is looking for the table in the dbo schema, not the users schema

ok, I think I understand the requirements. You need to store the last run time somewhere. Otherwise, if you start up at 14:15 on Tuesday, you have no idea if...

since this is happening in other environments, go to your test environment

run the query

record the time

run the query

record the time

run dbcc dropcleanbuffers

wait a few seconds

run the query

record the time

if possible,...

I believe you need to reference the column again on in the partition scheme. Seems redundant, but it does not need to match 100%.

CREATE CLUSTERED INDEX [ByCreatedWhen_idx] ON [dbo].[SXEvent]

([CreatedWhenUTC]...

Is the query scanning or seeking on the index? The optimizer will always list the perfect index, but in many cases a close index is good enough. If you are...

I want to make sure I understand before posting answers. You want the hourly schedule to show and run 3 times if the service was down for 3 hours?

The risks vary depending on other settings and configs but some are:

1) If you lock out a login, you only effect a single instance

2) if a password is leaked, they...

start up Performance Monitor

add SqlServer:Buffer Manager: Page Life expectancy to the graph

execute the query

If you see a sudden drop when the query is slow, then the data is not in...

No, every install of SQL should have its own service account. The only exception is failover clustering which shares services.