How would I measure the number of database writes on SQL Server 2008 R2
For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?
Sql-server – How to measure the number of database writes on SQL Server 2008 R2
sql-server-2008-r2
Related Solutions
What you are trying to do is a rolling upgrade sans upgrading the principal (kudos on leveraging technology). There is a link on BOL which details what to expect.
So the major differences I see on first glance is that the table scan on ARMiscIncome feeds a merge join in the slow version, which requires a sort:
But in the fast version it feeds a hash join (so no sort is required):
This sort is the highest overall cost in the slow plan, so I might suggest either finagling a hash join there (this could be simply due to different row counts in the two systems, even if statistics are out of date), or adding an index, such as this one which satisfies the output:
CREATE INDEX SortHelper ON dbo.ARMiscIncome(GuestID)
INCLUDE (Amount, ChargeCode, DateEntered, Description, TranDate);
(Or changing the clustered index on that table.)
Usual caveats apply, e.g. adding/changing an index may help this specific query, but may not help others, and/or may cause undue stress on DML operations. But certainly worth thoroughly testing IMHO.
As an aside, I was able to find the most expensive operator by comparing the plans in SentryOne Plan Explorer. This is a free tool that makes exactly this type of investigation a lot more painless than it is in Management Studio.
Related Question
- Sql-server – Setting up SQL Server 2008 R2
- Sql-server – SQL Server 2008 R2 – Weekly / Monthly Statistics
- Sql-server – How to change the number of @schedule_uid=N’????’ to re-create a SQL Server Agent Job
- Sql-server – How to find number of days since beginning of fiscal month
- Sql-server – Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2
- Sql-server – SQL Server 2008 Version of rows between… preceding and current row
Best Answer
I would look at using extended events. Truthfully I have not worekd with these and it's one of those things I have not sat down and tried to learn...yet. So I cannot give you an exact example of how you would impliment with your example.
The advantage to these over trace files is no performance hit. They were specifically designed with performance in mind. So letting it collect information over a long period should not be a problem. You can check out using it for monitoring system activity here. Jonathan Keyhayias did a good month-long series on using extended events. The first day gives a good overview of them here.
He also created a SSMS add-in that makes it a little easier to work with the Extended Events sessions.