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.