Time to start tuning queries - ANY that may be doing table scans on large tables that could otherwise be doing index seeks/bookmark lookups. I have seen any number...

Perhaps parallelism is hurting here. Check for CXPACKET waits using waitstats analysis. Do you have hyperthreaded cpus by any chance? Also try running the query with OPTION...

Sorry, I do not. I do have some insider knowledge as an MVP, but if I told you that stuff I would have to kill you. 😀

See SET SHOWPLAN ON in BOL. Also in Profiler, under the Performance event, check out Execution Plan, Show Plan All, Show Plan Statistics and Show Plan Text.

3 to 96 seconds is probably more than just slow IO. I would be looking for blocking. sp_who2 active is the quickest way to detect that.

I would use SET STATISTICS IO ON and execute in SSMS. Also use profiler to get good IO metrics as well as CPU, duration, rowcounts. Use the stored...

sudhakara (6/2/2008)

---

Hi ,

I got Dead lock error message from error log file . its showing line number and object id .But that object having the inner procedures because of...

Probably don't have enough RAM on the box (or allocated to SQL Server). Way too many read-aheads. Disk system seems slow too, but that could be just because...

Are all foreign key columns indexed (the ones in the other tables)?

Aaron West (5/27/2008)

---

The multiplications (column * 1) are only in the SELECT column list, not in the WHERE clause or the JOINs, so they should have no effect on what...

>>Why are you mulitplying lots of the columns by "1"? Destroys the ability to use an index...

I don't think that is true. It probably just prevents the...

Jeffrey Williams (5/23/2008)

---

Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will...

This is a VERY...

Are you reBUILDING the indexes, or reORGing them? a rebuild will redo the stats and that WILL force all associated sprocs to compile their first execution. IIRC a...

Ola Hallengren (5/22/2008)

---

In SQL Server 2005 you can also see the page_count in the dynamic management view

sys.dm_db_index_physical_stats.

I have a stored procedure that is using sys.dm_db_index_physical_stats to dynamically rebuild / reorganize...