Why so many reads?
-
I ran the DB Tuning Advisor on this and a couple other tables and it recommended 0 changes.
😉
I KNOW that something needs to be done to this table as well as a couple others but not sure what to do.
-
MrBaseball34 (8/19/2008)I'm sorry, that was from a different DB than the one that I was running the script on.
I have placed code in the script to remove IX_ptLabResults_2, anyway.
Any chance we can get the Query Plan from the actual execution environment?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
The SQLPLAN posted is from the correct DB and Table.
It's just that the indexes listed were from the same table on a different DB.
The correct indexes are in the screenshot.
-
MrBaseball34 (8/19/2008)
I ran the DB Tuning Advisor on this and a couple other tables and it recommended 0 changes....
I KNOW that something needs to be done to this table as well as a couple others but not sure what to do.
Yeah, in this case indexes are the problem, not the solution.
The issue here is that the _UniqueRecord index needs to be rebuilt and resorted because all of the MRN are (potentially) being changed and it is the first column of that key. By the SQLPLAN's own estimates, it would take at least 2GB just to store the raw data for this, let alone the index pointers and any Sort overhead.
And looking at the column definitions, made it clear that the actual situation may be ,much worse:
[MRN] [varchar](25) NULL,
[Test] [varchar](255) NOT NULL,
[Description] [varchar](255) NULL,
[DateofTest] [datetime] NOT NULL,
[ObsID] [varchar](255) NOT NULL,
Three of the fields of this key are Varchar(255) making this a very obese index indeed. It will not sort or perform well at all compared to leaner indexes. Now I know some tricks to improve the sorting time and reduce the memory need for an explicit sort with this problem, however as this is an internal SQL Server sort, they are not going to apply here. So that leaves,...
1) Improve this index somehow. That means changing its definition. If it can be done, it is probably the best option, however, there may be many valid reasons why it cannot or should not be done.
2) Get more memory. Allocate more from your server, or pushing other processes/jobs out, or just go and buy more.
3) Stupid Disk tricks: Make sure that your source DB's MDF, LDF, your TempDB's MDF and LDF are all of physically separate drives. Many find this difficult as it requires 4 different physical resource groupings. If you are sharing a SAN or NAS, make sure that none of the other servers sharing physical drives with you are active when you run (good luck). None of this will change the statistics, but it should make the disk performance about as fast as it can be.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
What percentage of these rows actually need to be rtrimmed?
I'm just wondering if adding
WHERE MRN <> RTRIM(MRN)
would make any difference.
It will still perform the initial scan, but if the index update is what is causing the problem, this may reduce I/O a bit.
-
Actually, I forgot the most important potential solution: RTRIM the MRN column before it gets stored in the first place.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
The majority of these columns were originally char(10) or char(9) and we are trying to
standardize the column types so, yes, we do need to trim them.
The VB.net class that sends the parameters to the Stored procedure has the property
defined as String. Maybe just changing the stored procedures will make this thing work
better without having to trim the values?
They have already been modified to insert, update and collect the data using the
varchar(25) data type so we can just modify the SPs to store or send back a trimmed
value and not have to worry about trimming them during an update like we are trying to do,
I believe.
-
That would be the direction that I would try.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply