Help with SLOW SQL Server performance

I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.

The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.

Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.

The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.

Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.

However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.

So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.

But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.

Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!The first lesson (ok, first after the "it is not your data" lesson), is to fix problems, and not theories. In order to pin down the exact problem, you have to amass approximately one mountain (mt) of data. Fire up perfmon on both the web server and the DB server. On both look at the following counters:

Memory: pages/sec should be < 100
Processor: %utilization should be < 75%
System: Processor Queue Length should be < 2 * number processors you have
System: Context Switches/sec should be < 10,000

If any of these are significantly out of whack, then you can try to drill down into any one of those.

On the database server side, Profiler and Query Analyzer are going to be your best friends. Trace
Stored Procedures: RPC Completed and
TSQL: SQL BatchCompleted
Look for anything with a duration over 1000. If you see a particular procedure/query commonly running over 1000, then start working over that query. A query that is run once or twice a day is not much to worry over.

In Query Analyzer, use sp_who2 to monitor for blockinig occasionally, and especially during slow times. It could be you just have some interface process that is locking up tables at times throughout the day.

Short of writing a book, this should get you started.|||MCrowley - Thanks for posting back and offering me a starting point on actualy fixing visual problems instead of guessing on what may be the cause. I'll look into the suggested tools and post back on my findings.

Much appreciated.|||Who's the Admin of the Box?

The FIRST thing I would do is to make sure all of your backups and transaction log dumps are ok and successful, by restoring the dumps to a test box.

Do you have restore procedures in place to recover in case of a problem? No? Thatw ould be my second task.

Do the developers use sa to build their code?

I'd revoke all sa access to the database as my third step and make sure only 1-3 individuals hav sa authority...hopefully non developers.

OK, so 4th I'd check for blocking (so_who2 active) and I'd also look at the locks (sp_locks). If you see a lot of table locks, you have a problem...most likely poorly design code.

I would the run a trace and make sure the trace writes to a sql server table.

Here's a template I made up. Make sure you change the host in the filter to a machine id that you are going to test on. Just make sure you change the extension back to .tdf.

I would however seriusly recommend that you hire a qualified dba.

what part of the world are you from?

I got some spare evenings...got some virtual PC going over there?|||The box is administrated by the hosting company primarily. We have limited access. It is our server, but it's on their network in their racks so they handle most of those adminstrative tasks such as updates/hardware/etc. Myself and the rest of the IT dept (6 of us now) all Domain Administration access in Active Directory and access to modify the DB as we see fit. But we don't have an in-house DBA and the guy who HAD the most knowledge got canned for just reasons. So...

We have Vertias in place for Back-Ups that are run every night. I don't know the ins and outs of whether they're full back ups or sequential or what have you, but I do know they work and we recently had one of our staff accidentally omit a where clause on an update which required us to restore a recent backup - so yes, that is tested and working just fine.

Developes us sa to build code? I'm assuming you mean the sa account in SQL Server, but again I could be wrong. I'm learning as I go here. There was "master" account created if you will that is used to access the server throughout all of our production and test code. This is not the "sa" account, but one that was created. When we develop code, we have our own logins and access rights and typically just right the code right in ASP or use Query Anaylzer to test first. When the code is put into production, it uses a general connection string using the master account I spoke of. I hope that answers this.

We do get blocking from time to time, but it's not every day. Still, it happens more often that I'd like. We also get frequent dissconnects. The company we were originally hosted with (we're 90% in house now except for this Server box) claims it's our Firewall and Router. We have no problems with lost connections from anywhere else so we fell this is an issue on their end. But getting back to the point. We do occassionally have blocks. I haven't really investigated them myself, but I can do that.

I've been running a few traces since the first suggestions posted and the trace gets to 15,000 rows in less than a minute. Using the Duration template, I've got to scroll up several pages from the bottom to stop seeing durations over 1000 so there's obviously some performance gains to be made there. Our worst one is a SP that routinely goes over 11,000 for the duration and is ran frequently.

Finally, I'm in the US - OHIO to be exact and we obviously do need a DBA. We've discussed doing that but would like to fix what we can on our own. So we can learn this stuff, and cut down on the cost and time the DBA will actually need to be here.

We do have and VPN and etc setup, but obviously I can't being giving out that kind of info over a message board nor do I have the authority to give it out at all. The company is pretty private with their data. But I DO really appreciate the suggestions and I'll have a look and the attached template.

Much thanks!|||Well I would open up that stored procedure and run a test on it with a SHOW EXECUTION PLAN ([CTRL]+K) and look at the results. Look for scans of any kind.

It could also be that you have a bad plan in PROCACHE. If it is a bad plan you'll need to recompile it.|||OHIO! Blind dude, get over there and help him out...|||Okay now this is where you've exceeded my brain capacity. I executed the SP in Query Analyzer and did the (CTRL + K) thing for showing the execution plan. I also just selected that from the action drop down, but I'm not getting anything so please teach me, wise one! :)

Also how can I check my PROCACHE. Sigh, I can just a see a link to Amazon.com for "SQL Server for dummies" coming my way.

Thanks again for all your help thus far.|||If you did both (selected it, then did CTRL+K), then I would most likely say that you turned it off.

Just go to the menu and select then run the sproc.

I did think you can see the cache, but you can clear it out. DBCC FREEOROCACHE...but that's not a good thing since it will cause everything to have to be recompiled.....wich will slow everything down the first time it's called.

Here's a test.

Make sure the SHOWPLAN is on

Execute the procedure in Query Analyzer...not the length of time and look at the plan

Then do sp_recompile <sprocname>

Execute the proc again. It will take longer because of the compil

Execute the sproc again, then note the time.

IF it is much fatser, then it's a bad plan.

If it's not then it still could be a bad plan and it has no choice.

You might want to show us the sproc.

But tell us what the plan says first

I'm kicking out, but I'll check in in the AM|||The Execution Plan drew out a huge grid diagram with icons and such. Is there anything specific you're looking for?

SPRoc - 1st Run. 40 seconds
SPRoc - 2nd Run. 28 seconds

--Force Recompile
SProc - 1st Run. 1 Mintue 28 seconds
Sproc - 2nd Run. 2nd Run. 30 seconds??
Sproc - 3rd, 4th, 5th, subsequent runs 7 - 15 Seconds! :)

Recompliling the SP seems to have helped, but with your logic that means I have a bad plan?? But I don't even know what a plan is. I'll do some studying and research tonight in prep for more respones.

Cheers!|||You'll be looking for any scans in the output. Also check for wide arrows (indicates many rows returned). Any large table that is getting scanned is probably a bad thing, unless this is a data warehouse/reporting kind of application. Cursors in the stored procedure are also a killer, and usually end up being the first target of any code re-writes.

If you can post the code of the stored procedure in question, we can take pot-shots at suggesting indexes, but those will depend pretty heavily on your data distribution. Have you tried the Index Tuning Wizard? It should be available in Profiler, and definitely should be in Books Online (which is SQL Server for SQL Server users). If you don't have Books Online (aka BOL) then find it on Microsoft's website, download, and install. We may sound like we know it all, but we cheat. We have BOL open from the minute we get in, to about 2 minutes before we leave.

Wait. Did I say that with my inside voice, or my outside voice?|||<div style=Columbo>
Oh, one other thing...
</div>

If you can see blocking, odds are it is a problem. Get to know dbcc inputbuffer (SPID), and try to chase down the blockers. The blockers could be half (or more) your problem.|||CREATE PROCEDURE [dbo].[Envox_AdjustCallLog]

AS

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

UPDATE
Envox_CallLog
SET StartTimeAdj=CallStartTime,
EndTimeAdj=CallEndTime,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='EST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-1,CallStartTime),
EndTimeAdj=DATEADD(hour,-1,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='CST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-2,CallStartTime),
EndTimeAdj=DATEADD(hour,-2,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='MST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-3,CallStartTime),
EndTimeAdj=DATEADD(hour,-3,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-5,CallStartTime),
EndTimeAdj=DATEADD(hour,-5,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST-2' AND Adjusted=0 AND Completed=1
GO

We have an Envox telephony Server in house. This SP is apparently adjusting all the logged call times based on their time zones. This is just one of many problem queries that are taking a while to run. I imagine the 3 joins per update on top of the ZipCodes table which is probably huge is the main issues.

I'm also not the phone guy, so I'd have to suggest any recommned changes to him as I'd be eaten alive if I messed with it on my own. The index tuning Wizard is running as we speak, so hopefully it comes back with some good suggestions.|||I can tell you right now, you're gonna see a scan on Properties

ON LEFT(Properties.Zip,5) = ZipCodes.Zip

Do this for us. Run the code below and post the Results

EXEC sp_spaceused Properties
GO
EXEC sp_spaceused ZipCodes
GO
EXEC sp_spaceused ZipCodes
GO

I can also tell you that I know what he's doing, he's turning everything into GMT Times. Now I've never been a big fan of derived columns, but man this is isure where this would be a good thing. Hell I might even say a TRIGGER might have been better than here. Also a derived column for the zip code so they don't need to use LEFT on the zip column in properties. That's called a stage 2 or nonsargable predicate and will always cause a scan.

Also, do you know how to script a table and get the DDL in Enterprise Mangler? Look at the sticky at the top of the forum here and read the instructions if you don't.

I betcha we can fix it up....|||Oh, and I like the error handling he has in there as well...is this run from and interface or a scheduled batch job...and I wonder about the very arbitrary

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

It's gotta be batch...

He really needed to have a batch window table so he doesn't miss anything...here's the problem with the way he's doing it...if the proc doesn't run for a while...there's no determining how long this damn thing will run...he's just leaving the earliest inserts alone...

Matt, wadda ya think, 2 mil inserts a day, TRIGGER? or Derived Column?|||Looks like a pretty easy kill, but if you have a test server, this will definitely need to be tested. Even if you don't have atest server, this should be tested. Try replacing all four updates with this:

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1

It will save you 3 go rounds on the table, which may translate to quartering the run time. Even if you get half an improvement, it should be good.|||Still gonna scan

But there will a perf boost...you change the old and new queries into selects to see what the return|||That's interesting. Why are the times be logged as different zones?

The server should be using the default time of the server.

How are you getting different time zones for the calls? This has to be a web app with 2 million hits a day, can't be distributed...

That's very curious how it's artichected...

Can you describe how the users us the app?|||OHIO! Blind dude, get over there and help him out...
Be happy to. My company is located in Columbus, and one of the services we offer is DBA outsourcing. I have a client that used to use a full-time dba, but we now cover their requirements with less than 10 hours per week.
If your company is interested in either consulting or dba support, just let me know.|||I doubt they have 2 mil inserts a day, but I could be wrong. Certainly not to a single table, I hope. You are right that this is bound to scan. You may be able to put an index on the Adjusted column. It is just a tad counterintuitive, but if the statistics are kept up to date, the optimizer should see only a very few rows with Adjusted=0, against tons with Adjusted = 1

After that, the first update has to be re-written to say something along the lines of

AND StartTime between dateadd (mi, 200, StartTimeAdj) and dateadd (mi, -200, StartTimeAdj)

provided there is an index on StartTime. That should be easy to switch around if the index is actually on StartTimeAdj.

I will have to think about the trigger vs. Derived Column. I heard somewhere that Computed Columns are not actually stored, but computed on each use. Not sure that is right, though.|||BOL has the following. That would be some heck of a computed column though. I wonder if a compouted can use an index? If it does it would help out with the zip problem.

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.|||The update statement seems to imply that each Invoice goes to a single JobID (sounds normal), but each Invoice has only a single property?

As for why the multiple Time Zones, who knows what business model decided this was a good idea. Seems that all calls are "synchronized" using the original invoice's zip code, so no fair going to the next time zone and calling in about your invoice.

I wonder how they handle Indiana and Arizona during Daylight Savings Time.|||I wonder if a compouted can use an index? If it does it would help out with the zip problem.Did you read what you posted from BOL ?!?!

If I were going to implement on multiple servers (or there was any chance that I might someday want to do so), I'd probably use UCT/GMT to store DATETIME values instead of local time. Using GUIDs and UCT are two key points I've found to keeping things simple as you scale up.

-PatP|||Awesome, awesome lots of feedback. I just returned from lunch so I haven't tried anything out yet, but I thought I'd post back what I could.

I can't get too technical, but our company does mystery shopping. What we're looking at right is obviously a stored procedure which converts times for different time zones.

We have at home employees that will call into our Envox Telephony server to pick up a job and then once connected there routed through some processes and an external number is dialed and they're connected to whom they're supposed to talking to. All the while their calls are recorded and the times they called are logged. Because the table is using time stamps, all the times are in the local time zone. This SProc, I presume is being used to convert all the times into the actual time for the call destination.

So if the employee was routed to California for their call, then the local time is converted to whatever time is would have been in Cali at the time of the call. Then is for reporting purposes obviously.

We have automated VB apps in place that run throughout the day doing different processes - this Sproc gets called from that automated app probably every 30 minutes or so. So roughly 48 times a day.

Name Rows Reserved Data Index Unused
Envox_CallLog 164627 30968 KB 16600 KB 88 KB 14280 KB
Properties 68628 131880 KB 82984 KB 10040 KB 38856 KB
Invoices 718950 3575416 KB 2871416 KB 165576 KB 538424 KB
ZipCodes 42567 5200 KB 5168 KB 16 KB 16 KB|||Computed columns can be indexed. I had to do it when a guy tried to join a varchar column to an int column. Not pretty, when the table was getting 2 - 300 lookups per second.

So, add a column to properties as left(zip, 5). So much for the international market, I guess. But then, that was a debate all to itself in the design forum, if I recall.|||Continuing on - Yes each invoices has a single JobID and single Property as each of our Jobs are placed on one property at a time. If we need to do more than one property, we do more than one job.

2 mil inserts a day? Not even. We've been going for 3 or 4 years now and we're upto 800,000 Jobs roughly, but there IS alot of activity going on. I should do some queries on how many tables and things we have and what kind of traffic is in and out of the SQL server all day. 2 mil inserts is probably a stretch. But I could bet we hit 500,000 transactions easy.|||It's all index seek baby

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Zip+4] char(10), Zip AS LEFT([Zip+4],5))
GO

SET ARITHABORT ON
GO

CREATE INDEX IX1 ON myTable99(Zip)
GO

INSERT INTO myTable99([Zip+4])
SELECT '07052-0000' UNION ALL
SELECT '12345-1234' UNION ALL
SELECT '00000-0000'
GO

--[CTRL]+K

SELECT *
FROM myTable99
WHERE Zip LIKE '0%'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

First thing I do is add a computed column zip in your properties table..it will have 0 impact to your application, and has the potential to speed up this existing query by changing it to reference that column.

That's a low impact/ high reward fix.|||Those tables look awfully lean on indexes (based on index size). Can you run sp_helpindex on each of those tables? Looks like this is coming down to design of the application, and how you can mitigate some of the bigger flaws. The tables are not huge, but as Brett points out, the scans are killing you.|||Well your scanning properties with 68,000 rows, and the zip fix I posted will fix that.

Now post the DDL, make sure you included all the contraints and indexes

Let me ask this, do you have a dev server where you can test these chnages out? Do you have the authority to ALTER database objetcs, like add a computed column?|||Okay, I'm a horrible typist so please forgive all the missed words and etc. I'll try to clean up my act a little.

Everything we do is on a testing server first and moved "live" once we're confident it's working as it should. This goes from IIS to SQL Server to Access to whatever. I do have permissions on the Database to do anything I need to do. I can do all of this stuff on the testing server, but I would have to go over the changes with the rest of the IT staff before I moved anything into production.

Let me run the HelpIndex SP and post back the findings.|||Envox_CallLog
PK_Envox_CallLog clustered, unique, primary key located on PRIMARY Call_ID

Properties
PK_Properties clustered, unique, primary key located on PRIMARY PropID
Properties15 nonclustered located on PRIMARY PropID, Property Name
Properties29 nonclustered located on PRIMARY PropID, ZIP, Country
Properties40 nonclustered located on PRIMARY PropID, ClientID
Properties41 nonclustered located on PRIMARY ClientID

Invoices
Invoices16 nonclustered located on PRIMARY ShopID
Invoices23 nonclustered located on PRIMARY ClientID, RcvdDate, AForm
Invoices31 nonclustered located on PRIMARY PropID, ShopID, ClientID
Invoices32 nonclustered located on PRIMARY ClientID, RcvdDate
Invoices34 nonclustered located on PRIMARY FU_Expires
Invoices35 nonclustered located on PRIMARY Select_Shop_Date
Invoices36 nonclustered located on PRIMARY FU_PDFed
Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID
PK_Invoices clustered, unique, primary key located on PRIMARY JOBID

ZipCodes does not contain any Indexes.|||These are useless, you have 2 other indexes that would take care of that

Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID

And you need a zip code index on the computed column like I posted.

Now what about the DDL|||I am surprised that the ZipCodes table has no indexes. I should think they would put a PK on the Zip column. Heck, add time_zone, and you have a covering index.

Just for curiosity, the Envox system is not in your control, but is the database? I know some vendors get awful uppity, when you start pointing out where indexes should be, and how table designs could be improved, etc.. They can even say your support contract is suspended for such things. Not a nice place to be caught when the fecal material is introduced into the atmospheric circulation machinery, if you know what I mean.|||Alter Table Properties
Add Zip5 as left(Zip,5)

I did that to add the computed column to the Properties table. Hopefully that was right. I then added an index to that column as requested. Running the old SProc as is, it was 17 seconds this time around. Using the new SProc which was altered to join on the new computed column instead of the old column

CREATE PROCEDURE [dbo].[Envox_AdjustCallLog2] AS

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON Properties.Zip5 = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1
GO

I got a run time of 2 seconds on the first run. However I'm conerned now. The old SProc affected 208 rows in total. The new one was 256 or so. It's like the old SProc is adjusting rows back the original time and then fixing them again.

Brett. I'm sorry, but can you explain to me what the DDL is? I think I missed that.|||Actually the Envox server is in house and we do control it, I just don't mess with it personally as I'm clueless when it comes to that sort of thing. However, I've given our top IT guy the link to this thread and he also handles the Envox server and he said this had been an interesting read and he would have no problems updating the SPRoc and adjust things as needed if it mean performance gains. So we're in the clear on that.|||For the DDL, look at the bottom in the FAQ section of this link here

http://www.dbforums.com/t1196943.html

As for as to why, you have to remeber the top part of the code

This

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

Sets the population to be affected in the subsequent DML. So the population is always changing...if you didn't do this you would get zero rows affected, unless soemthing else is modifying the Adjusted column.

Again, in my opinion, this is a poor way to run a batch window process.

I still think that you can not only have a computed column for the zip code (yeah for 2 seconds), but I think you could turn the adjusted time column in to a computed column, and then just eliminate this entire process altogether.

See, the problem with storing derived/data and storing it, is usually as soon as it's computed it becomes stale, and potentially out of synch with the real data.

No a computed column is derived at the time it's requested, so it's "fresh out of the oven"

Also, if the computed column doesn't work (there may be some issues with sub queries), there's nothing to say that this couldn't be a view.

Same concept but more flexibility.

I would hazard to say the majority of us don't use computed columns, so thanks for the exercise.|||You are talking about a computed column to strip off the basic five digit zip code?
You should store the zip and the zip4 separately to begin with. Or add a zip5 column and populate it on insert via a sproc or trigger, since it is probably not volatile data.|||Another thing to watch for, is if the insert into the property table uses one of my <sarcastic>favorite</sarcastic> constructs the insert into with no field list. Adding the computed field or any field would break that insert. Just one more thing to check.|||aye yup...so much for the low impact solution...I guess you could check by looking for

WHERE ROUTINE_DEFINITION NOT LIKE 'INTO%('
OR ROUTINE_DEFINITION NOT LIKE 'INSERT%('

But that wouldn't take into account code based sql

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99
SELECT 1,2
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col3 int
GO

INSERT INTO myTable99
SELECT 1,2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I tried posting earlier but I was having trouble accessing dbforums. I'll post up the DDL when I have time, but I've been really busy with meetings and stuff today. We're having a DBA come (Wohoo!!) to help us out and get us back on the right track.

Thanks to everyone who posted ideas and suggestions as it's helped me tremendously to better understand how SQL Server operates and things I can do to fine tune it. I still have much to learn!

To get back on topic, I'm 99.9% certain we don't have any Inserts in any of our code that does not use a field list. Actually, when I mentioned that to the IT guys a couple of them didn't even know you could do that, so I think we're ok there with the computed column.

I do agree the zip and +4 should have been store seperately, but I chalk that up to the programmers learning as they want and just adding stuff over the years. Like I said we're 4 years in and there are so many things that we'd like to fix. But when 80% of our operation is automated, it's tricky to just go in and change things.

Like for instance for EVERY client we have, we have 3 tables. All 3 tables are identical except for the name and the fact that each table holds the info for the client at different stages of the process. Clearly we should of had 1 table and just an additional flag that could be used to differentiate the data at different stages. But then how do you change all the processes that we have in place that rely on all those tables. Aye!

We need and overhaul badly.|||Congratulations on getting some relief in there. Take notes, on what he checks. Sounds like there may be another few stored procedures that will need a bit of tuning, but it is a whole lot easier to detect such problems when you are sitting in front of Profiler and Query Analyzer.

And thanks for being a good poster. Too many times we get "I have problem pls help" posts that we spend more time pulling the guy's teeth looking for clues as to why their hamster died.

Help with Performance question regarding IO Size

Hi,
I was wondering if anyone had any idea what is going on with the
following problem or if there is anything that could be checked on to
resolve it.
Basically been an ongoing performance problem with a sql 2005 64bit
itanium box which has been running slower than equivalent sql 2000
box.
The setup of this particular problem is that the server is attached to
a san with one large striped set of disks using a 2gb card. The table
i'm using is in a filegroup that is split across 4 files. To take out
of the equation any O/S fragmentation i've de-fragmented the 4 files.
I've also rebuilt the indexes to remove any fragmentation.
If i run a dbcc showcontig on the file i noticed that the latency of
transfer looks fine (under 20ms) for about 45-60secs and then shoots
up in one large jump to over 1 sec which kills performance. on further
investigation the IO size on the operation starts off at an 8k block
and then at the point latency jumps to over 1sec the block size goes
to 64k.
Why would this be? Wouldn't the IO size be constant during the
operation? or could it be the read size somehow be different between
one of the files in the filegroup. Not sure how this works or how you
would check it out. Anyone have any ideas?
ps. i've run SQLIO on the SAN and 8k sequential reads run in the
milliseconds and 64k IO's are running on average at about 0.025 to
0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
seems to run at 64k IO sizes.
Anyone have any ideas?
Thanks
Ian.
I don't think the fact that SQL Server was issuing 64K requests was
necessarily the root of the problem. Many SQL Server operations such as
read-ahead and checkpoints may issue larger than 8K I/O requests (or even
larger than 64K requests).
If I have to take a wild guess, I'd investigate whether your I/O performance
was killed by checkpoints. First of all, instead of looking at the I/O
transfer counters, I'd look at the more specific I/O read and write counters.
And, I'd check if the performance degradation coincides with checkpoints.
Include the counter SQLServer:Buffer Manager\Checkpoint pages/sec in your
perfmon tracking, and see if it's not zero when you experience the said
performance degradation.
Linchi
"ianwr" wrote:

> Hi,
> I was wondering if anyone had any idea what is going on with the
> following problem or if there is anything that could be checked on to
> resolve it.
> Basically been an ongoing performance problem with a sql 2005 64bit
> itanium box which has been running slower than equivalent sql 2000
> box.
> The setup of this particular problem is that the server is attached to
> a san with one large striped set of disks using a 2gb card. The table
> i'm using is in a filegroup that is split across 4 files. To take out
> of the equation any O/S fragmentation i've de-fragmented the 4 files.
> I've also rebuilt the indexes to remove any fragmentation.
> If i run a dbcc showcontig on the file i noticed that the latency of
> transfer looks fine (under 20ms) for about 45-60secs and then shoots
> up in one large jump to over 1 sec which kills performance. on further
> investigation the IO size on the operation starts off at an 8k block
> and then at the point latency jumps to over 1sec the block size goes
> to 64k.
> Why would this be? Wouldn't the IO size be constant during the
> operation? or could it be the read size somehow be different between
> one of the files in the filegroup. Not sure how this works or how you
> would check it out. Anyone have any ideas?
> ps. i've run SQLIO on the SAN and 8k sequential reads run in the
> milliseconds and 64k IO's are running on average at about 0.025 to
> 0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
> seems to run at 64k IO sizes.
> Anyone have any ideas?
> Thanks
> Ian.
>
|||Hi Linchi,
Thanks for the suggestion, i checked out what you said but
unfortunately there were no checkpoint operations during the
showcontig operation. Latency was 8ms with the 8k blocks until about 1
minute through the routine when i/o size went to 64k and latency
jumped to 1.2 secs.
Pulling my hair out now with this one. Performance can be so
unpredictable. I recently re-indexed 2 tables on the box with no-one
else on the system the 1st one had 120million rows and took 20 mins to
re-index, the 2nd with an identical structure but 135 million rows
(which i was hoping would take about 30-40 mins took 2.5 hours.
Looking at the counters latency was terrible during this period.
Just not sure what i can check now, I can get 20ms performance on the
sqlio tests at 64k but this dbcc operation causes latency to jump to
1.2 secs.
Any ideas?
Thanks
Ian.
|||I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests. So
I tried it on one of my servers with a drive presented from a SAN. I did
notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
Sec/Read for the 64K reads was ~160ms, far better than what you reported.
In my little test, I noticed that SQL Server was quite aggressive in
maintaining a long I/O queue length. The Current Disk Queue length for the
drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
In your case, I'd re-run sqlio.exe with the -o parameter set to a large
number (say 64) to see how the drive behaves with 64K reads (both sequential
and random).
Linchi
"ianwr" wrote:

> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.
>
|||By the way, when you ran your sqlio tests with 64K reads, what was the size
of the test file you used? The file size can make a huge difference in what
results you may get. See
http://www.sqlteam.com/article/benchmarking-disk-io-performance-size-matters
for more info.
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests. So
> I tried it on one of my servers with a drive presented from a SAN. I did
> notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
> and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
> Sec/Read for the 64K reads was ~160ms, far better than what you reported.
> In my little test, I noticed that SQL Server was quite aggressive in
> maintaining a long I/O queue length. The Current Disk Queue length for the
> drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
> In your case, I'd re-run sqlio.exe with the -o parameter set to a large
> number (say 64) to see how the drive behaves with 64K reads (both sequential
> and random).
> Linchi
> "ianwr" wrote:
|||What is the RAID set stripe size, NFTS allocation unit size for the formatt
of the partition and also did you sector align the partition using DISKPART?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b6a8aa4a-381d-401d-8b24-f1da081564a1@.d4g2000prg.googlegroups.com...
> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.
|||Guys,
Thanks for your responses. I'm going to run the sqlio test again this
morning as i think i ran them initially with the default 100mb file
when using the 64k size.
I don't think the partition was sector aligned, only because i
specifically read the manufacturers documentation on this and they had
said that there was no need to with the current firmware used.
I will re-run the tests and find out the remainder of the information
you asked for this morning.
Thanks for all your help guys. Much appreciated.
Ian.
|||Just ran the SQLIO tests and got the following for 64K reads with an
outstanding number of 64 :-
Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
min lat 4ms avg lat 48 max lat 124
Random Read file size 256mb I/O Sec 2743 MB/Sec 171
min lat 4ms avg lat 46 max lat 83
Sequential Read file size 2gb I/O Sec 2260 MB/Sec
141 min lat 4ms avg lat 56 max lat 427
Random Read file size 2gb I/O Sec 405 MB/Sec
25 min lat 4 avg lat 313 max lat 776
Sequential Read file size 8gb I/O Sec 974 MB/Sec
60 min lat 4ms avg lat 130 max lat 735
Random Read file Size 8gb I/O Sec 347 MB/Sec
21 min lat 21 avg lat 367 max lat 807
The san and server are used for a DW application and i would say that
the average file size for the databases is about 64gb but dont know if
this is relevant.
i've asked the SAN/Windows guy to get me the other parameters about
the stripe size, allocation unit size etc and will get back to you
with these. Just for future reference is there an easy way to pull off
these figures as i'm no windows guru ;-)
|||1) Your use of 256MB for a test file against a SAN is not appropriate. Many
SANs (heck, even HBAs) have caches larger than that. Even 2GB could be less
than a SAN cache, although from the numbers it appears that you are at least
starting to hit physical I/O. You really should test with a file (or files)
that are a reasonable fraction of the actual database sizes you anticipate.
2) Most SAN's come with software (usually a web-based) interface. It
usually provides both viewable and settable configuration stuff for a wide
variety of parameters. Perhaps your admins can hook you up with some
read-only version of this?
3) That is a pretty poorly performing SAN.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:db1fb7b5-2e75-40a3-adec-33d48df2db75@.f3g2000hsg.googlegroups.com...
> Just ran the SQLIO tests and got the following for 64K reads with an
> outstanding number of 64 :-
> Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
> min lat 4ms avg lat 48 max lat 124
> Random Read file size 256mb I/O Sec 2743 MB/Sec 171
> min lat 4ms avg lat 46 max lat 83
> Sequential Read file size 2gb I/O Sec 2260 MB/Sec
> 141 min lat 4ms avg lat 56 max lat 427
> Random Read file size 2gb I/O Sec 405 MB/Sec
> 25 min lat 4 avg lat 313 max lat 776
> Sequential Read file size 8gb I/O Sec 974 MB/Sec
> 60 min lat 4ms avg lat 130 max lat 735
> Random Read file Size 8gb I/O Sec 347 MB/Sec
> 21 min lat 21 avg lat 367 max lat 807
> The san and server are used for a DW application and i would say that
> the average file size for the databases is about 64gb but dont know if
> this is relevant.
> i've asked the SAN/Windows guy to get me the other parameters about
> the stripe size, allocation unit size etc and will get back to you
> with these. Just for future reference is there an easy way to pull off
> these figures as i'm no windows guru ;-)
>
>
|||Thanks Kevin for the response.
Unfortunately for me, the main guy who looks after the san now claims
he's no san expert and I suspect that they just bought in a ready made
solution from a 3rd party supplier. No-one on site seems to know
anything about the san and when asking what the raid stripe size or if
the partition had been sector aligned i just got a number of blank
looks lol ;-(
Fortunately, my contract only runs for a few more weeks here, so i'll
pass the information onto them and maybe they suggest getting a
specialist san consultant onboard to review and configure accordingly.
I know a new SAN will be ordered within the next few months so i guess
they will have to put up with these performance problems until then.
Thanks all your input and thoughts.
Ian.

Help with Performance question regarding IO Size

Hi,
I was wondering if anyone had any idea what is going on with the
following problem or if there is anything that could be checked on to
resolve it.
Basically been an ongoing performance problem with a sql 2005 64bit
itanium box which has been running slower than equivalent sql 2000
box.
The setup of this particular problem is that the server is attached to
a san with one large striped set of disks using a 2gb card. The table
i'm using is in a filegroup that is split across 4 files. To take out
of the equation any O/S fragmentation i've de-fragmented the 4 files.
I've also rebuilt the indexes to remove any fragmentation.
If i run a dbcc showcontig on the file i noticed that the latency of
transfer looks fine (under 20ms) for about 45-60secs and then shoots
up in one large jump to over 1 sec which kills performance. on further
investigation the IO size on the operation starts off at an 8k block
and then at the point latency jumps to over 1sec the block size goes
to 64k.
Why would this be? Wouldn't the IO size be constant during the
operation? or could it be the read size somehow be different between
one of the files in the filegroup. Not sure how this works or how you
would check it out. Anyone have any ideas?
ps. i've run SQLIO on the SAN and 8k sequential reads run in the
milliseconds and 64k IO's are running on average at about 0.025 to
0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
seems to run at 64k IO sizes.
Anyone have any ideas?
Thanks
Ian.I don't think the fact that SQL Server was issuing 64K requests was
necessarily the root of the problem. Many SQL Server operations such as
read-ahead and checkpoints may issue larger than 8K I/O requests (or even
larger than 64K requests).
If I have to take a wild guess, I'd investigate whether your I/O performance
was killed by checkpoints. First of all, instead of looking at the I/O
transfer counters, I'd look at the more specific I/O read and write counters
.
And, I'd check if the performance degradation coincides with checkpoints.
Include the counter SQLServer:Buffer Manager\Checkpoint pages/sec in your
perfmon tracking, and see if it's not zero when you experience the said
performance degradation.
Linchi
"ianwr" wrote:

> Hi,
> I was wondering if anyone had any idea what is going on with the
> following problem or if there is anything that could be checked on to
> resolve it.
> Basically been an ongoing performance problem with a sql 2005 64bit
> itanium box which has been running slower than equivalent sql 2000
> box.
> The setup of this particular problem is that the server is attached to
> a san with one large striped set of disks using a 2gb card. The table
> i'm using is in a filegroup that is split across 4 files. To take out
> of the equation any O/S fragmentation i've de-fragmented the 4 files.
> I've also rebuilt the indexes to remove any fragmentation.
> If i run a dbcc showcontig on the file i noticed that the latency of
> transfer looks fine (under 20ms) for about 45-60secs and then shoots
> up in one large jump to over 1 sec which kills performance. on further
> investigation the IO size on the operation starts off at an 8k block
> and then at the point latency jumps to over 1sec the block size goes
> to 64k.
> Why would this be? Wouldn't the IO size be constant during the
> operation? or could it be the read size somehow be different between
> one of the files in the filegroup. Not sure how this works or how you
> would check it out. Anyone have any ideas?
> ps. i've run SQLIO on the SAN and 8k sequential reads run in the
> milliseconds and 64k IO's are running on average at about 0.025 to
> 0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
> seems to run at 64k IO sizes.
> Anyone have any ideas?
> Thanks
> Ian.
>|||Hi Linchi,
Thanks for the suggestion, i checked out what you said but
unfortunately there were no checkpoint operations during the
showcontig operation. Latency was 8ms with the 8k blocks until about 1
minute through the routine when i/o size went to 64k and latency
jumped to 1.2 secs.
Pulling my hair out now with this one. Performance can be so
unpredictable. I recently re-indexed 2 tables on the box with no-one
else on the system the 1st one had 120million rows and took 20 mins to
re-index, the 2nd with an identical structure but 135 million rows
(which i was hoping would take about 30-40 mins took 2.5 hours.
Looking at the counters latency was terrible during this period.
Just not sure what i can check now, I can get 20ms performance on the
sqlio tests at 64k but this dbcc operation causes latency to jump to
1.2 secs.
Any ideas?
Thanks
Ian.|||I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests. S
o
I tried it on one of my servers with a drive presented from a SAN. I did
notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
Sec/Read for the 64K reads was ~160ms, far better than what you reported.
In my little test, I noticed that SQL Server was quite aggressive in
maintaining a long I/O queue length. The Current Disk Queue length for the
drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
In your case, I'd re-run sqlio.exe with the -o parameter set to a large
number (say 64) to see how the drive behaves with 64K reads (both sequential
and random).
Linchi
"ianwr" wrote:

> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.
>|||By the way, when you ran your sqlio tests with 64K reads, what was the size
of the test file you used? The file size can make a huge difference in what
results you may get. See
http://www.sqlteam.com/article/benc...ce-size-matters
for more info.
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests.
So
> I tried it on one of my servers with a drive presented from a SAN. I did
> notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
> and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
> Sec/Read for the 64K reads was ~160ms, far better than what you reported.
> In my little test, I noticed that SQL Server was quite aggressive in
> maintaining a long I/O queue length. The Current Disk Queue length for the
> drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
> In your case, I'd re-run sqlio.exe with the -o parameter set to a large
> number (say 64) to see how the drive behaves with 64K reads (both sequenti
al
> and random).
> Linchi
> "ianwr" wrote:
>|||What is the RAID set stripe size, NFTS allocation unit size for the formatt
of the partition and also did you sector align the partition using DISKPART?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b6a8aa4a-381d-401d-8b24-f1da081564a1@.d4g2000prg.googlegroups.com...
> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.|||Guys,
Thanks for your responses. I'm going to run the sqlio test again this
morning as i think i ran them initially with the default 100mb file
when using the 64k size.
I don't think the partition was sector aligned, only because i
specifically read the manufacturers documentation on this and they had
said that there was no need to with the current firmware used.
I will re-run the tests and find out the remainder of the information
you asked for this morning.
Thanks for all your help guys. Much appreciated.
Ian.|||Just ran the SQLIO tests and got the following for 64K reads with an
outstanding number of 64 :-
Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
min lat 4ms avg lat 48 max lat 124
Random Read file size 256mb I/O Sec 2743 MB/Sec 171
min lat 4ms avg lat 46 max lat 83
Sequential Read file size 2gb I/O Sec 2260 MB/Sec
141 min lat 4ms avg lat 56 max lat 427
Random Read file size 2gb I/O Sec 405 MB/Sec
25 min lat 4 avg lat 313 max lat 776
Sequential Read file size 8gb I/O Sec 974 MB/Sec
60 min lat 4ms avg lat 130 max lat 735
Random Read file Size 8gb I/O Sec 347 MB/Sec
21 min lat 21 avg lat 367 max lat 807
The san and server are used for a DW application and i would say that
the average file size for the databases is about 64gb but dont know if
this is relevant.
i've asked the SAN/Windows guy to get me the other parameters about
the stripe size, allocation unit size etc and will get back to you
with these. Just for future reference is there an easy way to pull off
these figures as i'm no windows guru ;-)|||1) Your use of 256MB for a test file against a SAN is not appropriate. Many
SANs (heck, even HBAs) have caches larger than that. Even 2GB could be less
than a SAN cache, although from the numbers it appears that you are at least
starting to hit physical I/O. You really should test with a file (or files)
that are a reasonable fraction of the actual database sizes you anticipate.
2) Most SAN's come with software (usually a web-based) interface. It
usually provides both viewable and settable configuration stuff for a wide
variety of parameters. Perhaps your admins can hook you up with some
read-only version of this?
3) That is a pretty poorly performing SAN.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:db1fb7b5-2e75-40a3-adec-33d48df2db75@.f3g2000hsg.googlegroups.com...
> Just ran the SQLIO tests and got the following for 64K reads with an
> outstanding number of 64 :-
> Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
> min lat 4ms avg lat 48 max lat 124
> Random Read file size 256mb I/O Sec 2743 MB/Sec 171
> min lat 4ms avg lat 46 max lat 83
> Sequential Read file size 2gb I/O Sec 2260 MB/Sec
> 141 min lat 4ms avg lat 56 max lat 427
> Random Read file size 2gb I/O Sec 405 MB/Sec
> 25 min lat 4 avg lat 313 max lat 776
> Sequential Read file size 8gb I/O Sec 974 MB/Sec
> 60 min lat 4ms avg lat 130 max lat 735
> Random Read file Size 8gb I/O Sec 347 MB/Sec
> 21 min lat 21 avg lat 367 max lat 807
> The san and server are used for a DW application and i would say that
> the average file size for the databases is about 64gb but dont know if
> this is relevant.
> i've asked the SAN/Windows guy to get me the other parameters about
> the stripe size, allocation unit size etc and will get back to you
> with these. Just for future reference is there an easy way to pull off
> these figures as i'm no windows guru ;-)
>
>|||Thanks Kevin for the response.
Unfortunately for me, the main guy who looks after the san now claims
he's no san expert and I suspect that they just bought in a ready made
solution from a 3rd party supplier. No-one on site seems to know
anything about the san and when asking what the raid stripe size or if
the partition had been sector aligned i just got a number of blank
looks lol ;-(
Fortunately, my contract only runs for a few more weeks here, so i'll
pass the information onto them and maybe they suggest getting a
specialist san consultant onboard to review and configure accordingly.
I know a new SAN will be ordered within the next few months so i guess
they will have to put up with these performance problems until then.
Thanks all your input and thoughts.
Ian.

Help with Performance question regarding IO Size

Hi,
I was wondering if anyone had any idea what is going on with the
following problem or if there is anything that could be checked on to
resolve it.
Basically been an ongoing performance problem with a sql 2005 64bit
itanium box which has been running slower than equivalent sql 2000
box.
The setup of this particular problem is that the server is attached to
a san with one large striped set of disks using a 2gb card. The table
i'm using is in a filegroup that is split across 4 files. To take out
of the equation any O/S fragmentation i've de-fragmented the 4 files.
I've also rebuilt the indexes to remove any fragmentation.
If i run a dbcc showcontig on the file i noticed that the latency of
transfer looks fine (under 20ms) for about 45-60secs and then shoots
up in one large jump to over 1 sec which kills performance. on further
investigation the IO size on the operation starts off at an 8k block
and then at the point latency jumps to over 1sec the block size goes
to 64k.
Why would this be? Wouldn't the IO size be constant during the
operation? or could it be the read size somehow be different between
one of the files in the filegroup. Not sure how this works or how you
would check it out. Anyone have any ideas?
ps. i've run SQLIO on the SAN and 8k sequential reads run in the
milliseconds and 64k IO's are running on average at about 0.025 to
0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
seems to run at 64k IO sizes.
Anyone have any ideas?
Thanks
Ian.I don't think the fact that SQL Server was issuing 64K requests was
necessarily the root of the problem. Many SQL Server operations such as
read-ahead and checkpoints may issue larger than 8K I/O requests (or even
larger than 64K requests).
If I have to take a wild guess, I'd investigate whether your I/O performance
was killed by checkpoints. First of all, instead of looking at the I/O
transfer counters, I'd look at the more specific I/O read and write counters.
And, I'd check if the performance degradation coincides with checkpoints.
Include the counter SQLServer:Buffer Manager\Checkpoint pages/sec in your
perfmon tracking, and see if it's not zero when you experience the said
performance degradation.
Linchi
"ianwr" wrote:
> Hi,
> I was wondering if anyone had any idea what is going on with the
> following problem or if there is anything that could be checked on to
> resolve it.
> Basically been an ongoing performance problem with a sql 2005 64bit
> itanium box which has been running slower than equivalent sql 2000
> box.
> The setup of this particular problem is that the server is attached to
> a san with one large striped set of disks using a 2gb card. The table
> i'm using is in a filegroup that is split across 4 files. To take out
> of the equation any O/S fragmentation i've de-fragmented the 4 files.
> I've also rebuilt the indexes to remove any fragmentation.
> If i run a dbcc showcontig on the file i noticed that the latency of
> transfer looks fine (under 20ms) for about 45-60secs and then shoots
> up in one large jump to over 1 sec which kills performance. on further
> investigation the IO size on the operation starts off at an 8k block
> and then at the point latency jumps to over 1sec the block size goes
> to 64k.
> Why would this be? Wouldn't the IO size be constant during the
> operation? or could it be the read size somehow be different between
> one of the files in the filegroup. Not sure how this works or how you
> would check it out. Anyone have any ideas?
> ps. i've run SQLIO on the SAN and 8k sequential reads run in the
> milliseconds and 64k IO's are running on average at about 0.025 to
> 0.050 with the odd spike upto 0.3 but nothing like when the SQL Server
> seems to run at 64k IO sizes.
> Anyone have any ideas?
> Thanks
> Ian.
>|||Hi Linchi,
Thanks for the suggestion, i checked out what you said but
unfortunately there were no checkpoint operations during the
showcontig operation. Latency was 8ms with the 8k blocks until about 1
minute through the routine when i/o size went to 64k and latency
jumped to 1.2 secs.
Pulling my hair out now with this one. Performance can be so
unpredictable. I recently re-indexed 2 tables on the box with no-one
else on the system the 1st one had 120million rows and took 20 mins to
re-index, the 2nd with an identical structure but 135 million rows
(which i was hoping would take about 30-40 mins took 2.5 hours.
Looking at the counters latency was terrible during this period.
Just not sure what i can check now, I can get 20ms performance on the
sqlio tests at 64k but this dbcc operation causes latency to jump to
1.2 secs.
Any ideas?
Thanks
Ian.|||I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests. So
I tried it on one of my servers with a drive presented from a SAN. I did
notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
Sec/Read for the 64K reads was ~160ms, far better than what you reported.
In my little test, I noticed that SQL Server was quite aggressive in
maintaining a long I/O queue length. The Current Disk Queue length for the
drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
In your case, I'd re-run sqlio.exe with the -o parameter set to a large
number (say 64) to see how the drive behaves with 64K reads (both sequential
and random).
Linchi
"ianwr" wrote:
> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.
>|||By the way, when you ran your sqlio tests with 64K reads, what was the size
of the test file you used? The file size can make a huge difference in what
results you may get. See
http://www.sqlteam.com/article/benchmarking-disk-io-performance-size-matters
for more info.
Linchi
"Linchi Shea" wrote:
> I don't know the internals of how DBCC SHOWCONTIG issues its I/O requests. So
> I tried it on one of my servers with a drive presented from a SAN. I did
> notice what you described, i.e. DBCC SHOWCONTIG initially issues 8K reads,
> and then bumped the I/O request size up to ~64K. In my case, the Avg Disk
> Sec/Read for the 64K reads was ~160ms, far better than what you reported.
> In my little test, I noticed that SQL Server was quite aggressive in
> maintaining a long I/O queue length. The Current Disk Queue length for the
> drive was >32 and reached into >100 for the duration of DBCC SHOWCONTIG.
> In your case, I'd re-run sqlio.exe with the -o parameter set to a large
> number (say 64) to see how the drive behaves with 64K reads (both sequential
> and random).
> Linchi
> "ianwr" wrote:
> > Hi Linchi,
> >
> > Thanks for the suggestion, i checked out what you said but
> > unfortunately there were no checkpoint operations during the
> > showcontig operation. Latency was 8ms with the 8k blocks until about 1
> > minute through the routine when i/o size went to 64k and latency
> > jumped to 1.2 secs.
> >
> > Pulling my hair out now with this one. Performance can be so
> > unpredictable. I recently re-indexed 2 tables on the box with no-one
> > else on the system the 1st one had 120million rows and took 20 mins to
> > re-index, the 2nd with an identical structure but 135 million rows
> > (which i was hoping would take about 30-40 mins took 2.5 hours.
> > Looking at the counters latency was terrible during this period.
> >
> > Just not sure what i can check now, I can get 20ms performance on the
> > sqlio tests at 64k but this dbcc operation causes latency to jump to
> > 1.2 secs.
> >
> > Any ideas?
> >
> > Thanks
> >
> > Ian.
> >|||What is the RAID set stripe size, NFTS allocation unit size for the formatt
of the partition and also did you sector align the partition using DISKPART?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b6a8aa4a-381d-401d-8b24-f1da081564a1@.d4g2000prg.googlegroups.com...
> Hi Linchi,
> Thanks for the suggestion, i checked out what you said but
> unfortunately there were no checkpoint operations during the
> showcontig operation. Latency was 8ms with the 8k blocks until about 1
> minute through the routine when i/o size went to 64k and latency
> jumped to 1.2 secs.
> Pulling my hair out now with this one. Performance can be so
> unpredictable. I recently re-indexed 2 tables on the box with no-one
> else on the system the 1st one had 120million rows and took 20 mins to
> re-index, the 2nd with an identical structure but 135 million rows
> (which i was hoping would take about 30-40 mins took 2.5 hours.
> Looking at the counters latency was terrible during this period.
> Just not sure what i can check now, I can get 20ms performance on the
> sqlio tests at 64k but this dbcc operation causes latency to jump to
> 1.2 secs.
> Any ideas?
> Thanks
> Ian.|||Guys,
Thanks for your responses. I'm going to run the sqlio test again this
morning as i think i ran them initially with the default 100mb file
when using the 64k size.
I don't think the partition was sector aligned, only because i
specifically read the manufacturers documentation on this and they had
said that there was no need to with the current firmware used.
I will re-run the tests and find out the remainder of the information
you asked for this morning.
Thanks for all your help guys. Much appreciated.
Ian.|||Just ran the SQLIO tests and got the following for 64K reads with an
outstanding number of 64 :-
Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
min lat 4ms avg lat 48 max lat 124
Random Read file size 256mb I/O Sec 2743 MB/Sec 171
min lat 4ms avg lat 46 max lat 83
Sequential Read file size 2gb I/O Sec 2260 MB/Sec
141 min lat 4ms avg lat 56 max lat 427
Random Read file size 2gb I/O Sec 405 MB/Sec
25 min lat 4 avg lat 313 max lat 776
Sequential Read file size 8gb I/O Sec 974 MB/Sec
60 min lat 4ms avg lat 130 max lat 735
Random Read file Size 8gb I/O Sec 347 MB/Sec
21 min lat 21 avg lat 367 max lat 807
The san and server are used for a DW application and i would say that
the average file size for the databases is about 64gb but dont know if
this is relevant.
i've asked the SAN/Windows guy to get me the other parameters about
the stripe size, allocation unit size etc and will get back to you
with these. Just for future reference is there an easy way to pull off
these figures as i'm no windows guru ;-)|||1) Your use of 256MB for a test file against a SAN is not appropriate. Many
SANs (heck, even HBAs) have caches larger than that. Even 2GB could be less
than a SAN cache, although from the numbers it appears that you are at least
starting to hit physical I/O. You really should test with a file (or files)
that are a reasonable fraction of the actual database sizes you anticipate.
2) Most SAN's come with software (usually a web-based) interface. It
usually provides both viewable and settable configuration stuff for a wide
variety of parameters. Perhaps your admins can hook you up with some
read-only version of this?
3) That is a pretty poorly performing SAN.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:db1fb7b5-2e75-40a3-adec-33d48df2db75@.f3g2000hsg.googlegroups.com...
> Just ran the SQLIO tests and got the following for 64K reads with an
> outstanding number of 64 :-
> Sequential Read file size 256mb I/O Sec 2611 MB/Sec 163
> min lat 4ms avg lat 48 max lat 124
> Random Read file size 256mb I/O Sec 2743 MB/Sec 171
> min lat 4ms avg lat 46 max lat 83
> Sequential Read file size 2gb I/O Sec 2260 MB/Sec
> 141 min lat 4ms avg lat 56 max lat 427
> Random Read file size 2gb I/O Sec 405 MB/Sec
> 25 min lat 4 avg lat 313 max lat 776
> Sequential Read file size 8gb I/O Sec 974 MB/Sec
> 60 min lat 4ms avg lat 130 max lat 735
> Random Read file Size 8gb I/O Sec 347 MB/Sec
> 21 min lat 21 avg lat 367 max lat 807
> The san and server are used for a DW application and i would say that
> the average file size for the databases is about 64gb but dont know if
> this is relevant.
> i've asked the SAN/Windows guy to get me the other parameters about
> the stripe size, allocation unit size etc and will get back to you
> with these. Just for future reference is there an easy way to pull off
> these figures as i'm no windows guru ;-)
>
>|||Thanks Kevin for the response.
Unfortunately for me, the main guy who looks after the san now claims
he's no san expert and I suspect that they just bought in a ready made
solution from a 3rd party supplier. No-one on site seems to know
anything about the san and when asking what the raid stripe size or if
the partition had been sector aligned i just got a number of blank
looks lol ;-(
Fortunately, my contract only runs for a few more weeks here, so i'll
pass the information onto them and maybe they suggest getting a
specialist san consultant onboard to review and configure accordingly.
I know a new SAN will be ordered within the next few months so i guess
they will have to put up with these performance problems until then.
Thanks all your input and thoughts.
Ian.|||Definitely encourage your client to get a subject matter expert in BEFORE
they spec, provision and configure the new SAN. Doing otherwise is just
pissing money way - pardon my french. :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:de75c6a6-d86c-4b64-8643-3b9426237202@.s8g2000prg.googlegroups.com...
> Thanks Kevin for the response.
> Unfortunately for me, the main guy who looks after the san now claims
> he's no san expert and I suspect that they just bought in a ready made
> solution from a 3rd party supplier. No-one on site seems to know
> anything about the san and when asking what the raid stripe size or if
> the partition had been sector aligned i just got a number of blank
> looks lol ;-(
> Fortunately, my contract only runs for a few more weeks here, so i'll
> pass the information onto them and maybe they suggest getting a
> specialist san consultant onboard to review and configure accordingly.
> I know a new SAN will be ordered within the next few months so i guess
> they will have to put up with these performance problems until then.
> Thanks all your input and thoughts.
> Ian.
>|||lol ... they are good at that already ;-)
but thanks for all your help anyway, i think there is only you and
linchi who give good constructive comments when it comes to SAN
related issues.
Ian.|||Thanks for the compliment. Nice to be mentioned in the same sentence with
Linchi too! :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:6022022f-f7bc-4cb2-a6d5-91b117bbdd43@.z17g2000hsg.googlegroups.com...
> lol ... they are good at that already ;-)
> but thanks for all your help anyway, i think there is only you and
> linchi who give good constructive comments when it comes to SAN
> related issues.
> Ian.|||ps. Just out of curiosity more than anything and this is for linchi
and kevin, on an 8gb file, based on oustanding depth of 64 and io size
of 64k what kind of latency and throughputs are you getting on your
SANs? Just so i can show the guys here what kind of speeds then can
expect.
i know the setups of hardware and how its been striped will be
different, but if i can show them what speeds it can get upto maybe
they will think about getting a specialist in when the new san
arrives.
Thanks for all your help
ian.|||Unfortunately this question really can't be answered without more
information and controls. It is like apples and pomegranates otherwise.
:-) How many spindles is the primary factor. Rough estimate is for between
100 and 150 IO/sec per spindle IIRC once you get a data volume that
outstrips the SAN/HBA cache. Make sure you test 32 for queue depth too, not
just assuming that 64 is optimal. Any reasonable san should provide you
with many thousands, if 5 or even 6 figures, of I/Os per second. I would
shoot for latency in the low double digits.
Also see here for some good info:
http://www.sqlteam.com/article/benchmarking-disk-io-performance-size-matters
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:685ea213-2d43-477a-901c-47a7c01cc7b9@.q77g2000hsh.googlegroups.com...
> ps. Just out of curiosity more than anything and this is for linchi
> and kevin, on an 8gb file, based on oustanding depth of 64 and io size
> of 64k what kind of latency and throughputs are you getting on your
> SANs? Just so i can show the guys here what kind of speeds then can
> expect.
> i know the setups of hardware and how its been striped will be
> different, but if i can show them what speeds it can get upto maybe
> they will think about getting a specialist in when the new san
> arrives.
> Thanks for all your help
> ian.|||thank you.

Help with Performance on a Conditional Trigger

I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.

Take a look and see if you can help me please.There are some major issues with your trigger. The length and repetetiveness is just symptomatic.

When you evaluate statements like:
IF (SELECT RESV_TOTAL FROM inserted) = 0
...you need to keep in mind that the trigger executes once for every insert statement, not once for every inserted record. If you load 100 records into this table the trigger is still going to fire only once, and thus SELECT RESV_TOTAL FROM inserted will return more than one record and can't be compared to the scalar value "0".

And regarding:
IF (SELECT COUNT(*) FROM RESERVATION_MASTER RM, inserted
WHERE RM.BOOK_NO = inserted.BOOK_NO) >= @.@.ROWCOUNT
...I can't even be sure which last successful transaction @.@.ROWCOUNT refers to, and I'm not convinced you can either.

I suggest you back off, think about your application process a bit more, and try to write up what you are trying to accomplish as succinctly as possible. Having a clear picture of Point A and Point B often helps in devising the best route from Point A to Point B.|||it certainly does appear that you're not quite consistent in the method of identifying your conditions. but i'd say that can be fixed once you follow blindman's advice. what cannot be fixed is the problem that you addressed us with in the first place, - "it seems to become slower and slower as the final table grows larger."

if you can revamp your insert process by referencing that final table through a lefty outer join (left that is.) i'd even go further, i'd create a view using that join, and bcp records out. then, i'd drop the trigger all together and just use bulk insert. after all, at that point i only have records that i need.

Help with Logical Fragmentation

I am having a look at the performance issues on a clients Website analysis
tool database which currently stands at 440 GB and as a result have not run
the optimisation or integrity checking parts of the maintenance plan for a
long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
(finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
the table but did not touch the first row. So what I want to know is A) What
this first row represents and check that it should be as low as possible and
B) what command will reduced it.
Thanks in Advance Russell
This is on a SQL Server 2000 SP3a server.
"Russell" wrote:

> I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
> the table but did not touch the first row. So what I want to know is A) What
> this first row represents and check that it should be as low as possible and
> B) what command will reduced it.
> Thanks in Advance Russell
|||The largest table with issue is globalvb_Visits and when I run DBCC
SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
output I get is listed below.
ObjectId
1382950102
IndexName
IndexId
0
Level
0
Pages
547631
Rows
27929035
MinimumRecordSize
149
MaximumRecordSize
149
AverageRecordSize
149
ForwardedRecords
0
Extents
68465
ExtentSwitches
68464
AverageFreeBytes
395.0390015
AveragePageDensity
95.11936188
ScanDensity
99.9839334
BestCount
68454
ActualCount
68465
LogicalFragmentation
99.99981689
ExtentFragmentation
27.22558975
|||From the results ...it looks globalvb_Visits has no indexes?
The scandensity looks fine.
That should be between the 75 and 100%.
"Russell" wrote:

> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>
|||I would also check out your OS disk file fragmentation. If default settings
for size and growth were used for the initial database, you will have
several hundred thousand 1MB fragments of your data file all over the disk.
I had a client with that problem and a simple defrag resulted in an 18%
throughput improvement!!
TheSQLGuru
President
Indicium Resources, Inc.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
>I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not
> run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I
> presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> on
> the table but did not touch the first row. So what I want to know is A)
> What
> this first row represents and check that it should be as low as possible
> and
> B) what command will reduced it.
> Thanks in Advance Russell
|||I will have a look at the disk fragmentation.
There are 4 indexes on the table and running DBCC DBRENINDEX against the
table reduces their logical fragmentation to 0%.
"TheSQLGuru" wrote:

> I would also check out your OS disk file fragmentation. If default settings
> for size and growth were used for the initial database, you will have
> several hundred thousand 1MB fragments of your data file all over the disk.
> I had a client with that problem and a simple defrag resulted in an 18%
> throughput improvement!!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
>
>
|||Hi Russell
IndexID 0 indicates this table is a heap, which by definition has no
organization to it.
The purpose of DBCC INDEXDEFRAG is to make the physical order of pages in an
INDEX match the logical order, but since your table has no logical order,
DBCC INDEXDEFRAG will have no effect.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:9E83AC0A-1706-46A9-A525-DCD1B8E9B392@.microsoft.com...
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>

Help with Logical Fragmentation

I am having a look at the performance issues on a clients Website analysis
tool database which currently stands at 440 GB and as a result have not run
the optimisation or integrity checking parts of the maintenance plan for a
long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
(finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
the table but did not touch the first row. So what I want to know is A) What
this first row represents and check that it should be as low as possible and
B) what command will reduced it.
Thanks in Advance RussellThis is on a SQL Server 2000 SP3a server.
"Russell" wrote:
> I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
> the table but did not touch the first row. So what I want to know is A) What
> this first row represents and check that it should be as low as possible and
> B) what command will reduced it.
> Thanks in Advance Russell|||The largest table with issue is globalvb_Visits and when I run DBCC
SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
output I get is listed below.
ObjectId
1382950102
IndexName
IndexId
0
Level
0
Pages
547631
Rows
27929035
MinimumRecordSize
149
MaximumRecordSize
149
AverageRecordSize
149
ForwardedRecords
0
Extents
68465
ExtentSwitches
68464
AverageFreeBytes
395.0390015
AveragePageDensity
95.11936188
ScanDensity
99.9839334
BestCount
68454
ActualCount
68465
LogicalFragmentation
99.99981689
ExtentFragmentation
27.22558975|||From the results ...it looks globalvb_Visits has no indexes?
The scandensity looks fine.
That should be between the 75 and 100%.
"Russell" wrote:
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>|||I would also check out your OS disk file fragmentation. If default settings
for size and growth were used for the initial database, you will have
several hundred thousand 1MB fragments of your data file all over the disk.
I had a client with that problem and a simple defrag resulted in an 18%
throughput improvement!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
>I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not
> run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I
> presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> on
> the table but did not touch the first row. So what I want to know is A)
> What
> this first row represents and check that it should be as low as possible
> and
> B) what command will reduced it.
> Thanks in Advance Russell|||I will have a look at the disk fragmentation.
There are 4 indexes on the table and running DBCC DBRENINDEX against the
table reduces their logical fragmentation to 0%.
"TheSQLGuru" wrote:
> I would also check out your OS disk file fragmentation. If default settings
> for size and growth were used for the initial database, you will have
> several hundred thousand 1MB fragments of your data file all over the disk.
> I had a client with that problem and a simple defrag resulted in an 18%
> throughput improvement!!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
> >I am having a look at the performance issues on a clients Website analysis
> > tool database which currently stands at 440 GB and as a result have not
> > run
> > the optimisation or integrity checking parts of the maintenance plan for a
> > long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> > TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> > blank Indexname and also reports a 99.993% Logical Fragmentation. I
> > presume
> > that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> > (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> > on
> > the table but did not touch the first row. So what I want to know is A)
> > What
> > this first row represents and check that it should be as low as possible
> > and
> > B) what command will reduced it.
> >
> > Thanks in Advance Russell
>
>|||Hi Russell
IndexID 0 indicates this table is a heap, which by definition has no
organization to it.
The purpose of DBCC INDEXDEFRAG is to make the physical order of pages in an
INDEX match the logical order, but since your table has no logical order,
DBCC INDEXDEFRAG will have no effect.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:9E83AC0A-1706-46A9-A525-DCD1B8E9B392@.microsoft.com...
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>