mallikachowdhary 98955 (7/24/2014)

---

Hi ,

I have a issue regarding a query showing very late response on one server while it works perfectly on another.

I have a job defined in SQL server...

>> and restricted the log size to 30mb <<

That seems a little low, given that you might have a situation where one transaction takes a while to complete. Would...

You simply must get some answers to accurately resolve this:

1) What is the recovery model for your database? Bulk-logged, Full or Simple?

2) If not simple, at what time(s) during...

Igor Micev (7/23/2014)

---

ScottPletcher (7/23/2014)

---

I suspect there's a better clustering key, rather than the "default" identity, that might clear up a lot of your performance issues. Getting the best clustering...

And wishing you'd answered that matchbook ad for a technical school...

N_Muller (7/23/2014)

---

Transaction ID was not the best term - I guess I should have used Event ID :-). Event ID is naturally increasing, but there are many changes to...

N_Muller (7/23/2014)

---

Identity as a clustered index was not my first choice. The problem is that this is a high volume, high concurrency OLTP with thousands of data inserts per minute....

If you're willing to push back against the myth that identity should be default clustering key, and base the clustering key(s) choice on data instead, you can change the bolded...

I suspect there's a better clustering key, rather than the "default" identity, that might clear up a lot of your performance issues. Getting the best clustering key(s) is the...

I'd definitely restore and use the backup before I deleted the original files.

But rather than detach and delete the files yourself, just do a DROP DATABASE, which would be a...

It seems you do indeed have the proper clustering key.

Next I'd look at the missing index stats from SQL. You may be able to cover a large % of...

Admingod (7/22/2014)

---

Thanks it worked! So here we are not moving the file to different location.

That means we are just altering the file size with following command.

ALTER DATABASE tempdb MODIFY...

You can adjust the tempdb file(s) size using the following commands. The changes will be in effect the next time SQL Server starts.

--EXEC tempdb.sys.sp_helpfile --to list the logical file...

Btw, for date/datetime, don't use "<=" on the end date, use "<".

In this case, instead of:

[DATE] <= (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))

do this:

[DATE] < (DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

I think that's one of the most value-added and differentiated...