The insert/calculation trigger/rollback will be more efficient (than insert/calculation trigger/commit/delete/delete trigger/commit), but it's really only going to matter if the server is under a severe load, or the delete trigger...

In this scenario, where you don't yet carry enough weight/experience to out-and-out declare that the index rebuilds are absolutely necessary, I suggest you utilise a test/dev environment. Take a...

Bhuvnesh (8/22/2010)

---

Jim McLeod (8/22/2010)

---

a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until...

Disabling the indexes and then rebuilding them also means you don't need extra space in your data file. For example, a 5 GB non-clustered index normally requires an extra...

Yep - you got it. There's a setting in Management Studio - Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved - Non XML Data, which...

TDP (8/13/2010)

---

Thanks Paul. I will look into your suggestions.

Jim, yes, it is the actual setting for the max memory that changed. 🙂 It is also very odd that it was...

Ah, Paul raises a point that I missed, as it's not too clear. Is the Max Server Memory setting actually changing, or is the memory usage of the server...

The Max Server Memory setting is the total size of the buffer pools only - it doesn't include other uses that SQL Server has for memory.

From http://support.microsoft.com/kb/321363 (specific to 2005...

This sounds like a perfect job for Profiler. There's a couple of ways you could trace this.

For stored procedures, you'll want to capture RPC:Completed and SP:StmtCompleted events with Profiler....

You can use sp_procoption to run a stored procedure on startup, but I'd prefer to have a SQL Agent job that frequently checks to see if the trace is running....

This is good advice. Use a little...

It's worth thinking about what you're counting. In your first (working) example, you are counting all rows that are in the results from SELECT FROM TableA. This will...

Assuming 2005 (given the forum), there's a checkbox for "Workstation components, Books Online and development tools". On this screen, there's an "Advanced" button, which brings up a treeview that...

Thanks for all the answers, both here and privately.

From initial testing, it does appear that SQL Server on Windows 2008 manages the memory pretty well - I fired up SSMS...

Hi Dominic,

It's possible that the (x rows affected) message is the problem. According to Books Online for sqlcmd:

When multiple results are returned, sqlcmd prints a blank line between each...