186 reputation
4
bio website datarealized.com
location Denver, CO
age
visits member for 2 years
seen Jan 27 '13 at 17:13

Dec
30
answered Rebuilding system and tables, should I change primary key to int?
Dec
6
comment considerations when using triggers on a replicated (target) database
.... The last (good) option, would be to use SSIS for this as well - those options however all depend on how latent the data can be - if reporting can work with data which is 10 or 15 minutes old, then writing a process outside of using a trigger based approach would be the option that I'd personally target first.
Dec
6
comment considerations when using triggers on a replicated (target) database
It depends on the type of trigger - if you use an after insert trigger, you will be fine - however, I would put your trigger logic in a try/catch block. That way, if there is an error with your insert, you know that your generated table is out of synch. It's also important to keep in mind that with replication, when you need to re-initialize, it's possible that you will need to re-create your trigger - this depends, of course, on how you re-initialize. Another option, would be to write a simple stored proc which moves/generates this data for you an a set interval - i.e. every 10 minutes.
Dec
5
comment Incredibly slow query performance
Zane, I'd begin this one with populating a temporary table with the SR result set - and then join your nested statements to that SR result set. I haven't gone through the entire statement in detail but at first blush it would return the correct data - to the point of improving performance, it would have a marked impacted I believe.
Nov
30
comment SQL Server Cache Flush and Disk I/O
Also - check to see if your database file or your log file is growing during the time. In addition to that, gain some metrics from your SAN/NAS, etc... I'm wondering if you are flushing your write cache. And, as Mike mentioned, does the read/write throughput (i.e. batch requests per second) decrease during this heavy write time?
Nov
19
comment Large set of execution plans hangs up SSMS
Wow. Thank you for the heads up on this Aaron. I wonder if it's simply capturing the reads for one of the processes
Nov
16
comment SQL Server 2008 R2 Database - Why is the database not using free space before growing again?
Does your database have mulitple file groups? If so, perhaps one of them is growing an another isn't. this would explain the static free space and growth of the database.
Nov
16
comment Is it possible to increase query performance on a narrow table with millions of rows?
I'm not sure why I missed this earlier but use row compression on the clustered index and the non-clustered index as well. I just did a quick test with your table and here's what I found: I created a set of data (5.8 million rows) in the table defined above. I compressed (row) the clustered and nonclustered index. logical reads, based on your exact query, decreased from 2,074 to 1,433. That's a significant decrease and I'm confident that alone would help you out - and it's very low risk.
Nov
16
comment Is it possible to increase query performance on a narrow table with millions of rows?
In that case, keep the datetime column, add the int column for date (since your range is based on the date element and not the time element). You could also consider using the TIME datatype and then, effectively split the time apart from the date. In that manner, your data footprint is smaller and you still have the Time element of the column.
Nov
16
answered index maintenance strategy if few insertion points relative to # of rows
Nov
16
comment SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?
Another interesting counter to look at is in perfmon:SQLServer:Buffer Node:. The counters in that family of interest are Foreign Pages, Free Pages, Page Life Expectancy, Total Pages, Target Pages and Stolen Pages. I'm guessing, that before you implemented the trace flag, that you had a very high quantity of foreign pages - Do you have TF 834 enabled? If so, I've found that it doesn't allocate memory to each numa node in a balanced fashion which leads to a very high quantity of expensive remote numa node memory lookups. The system I had this issue on contained 1TB of ram at the time.
Nov
16
answered Is it possible to increase query performance on a narrow table with millions of rows?
Nov
16
answered Determining that Indexes on a table are unused
Nov
16
awarded  Supporter
Nov
16
answered Large set of execution plans hangs up SSMS
Nov
16
awarded  Custodian
Nov
16
reviewed Approve suggested edit on SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?
Nov
16
awarded  Teacher
Nov
15
awarded  Editor
Nov
15
revised SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?
deleted 13 characters in body