Why is my SP running like a dog?

[1DMF]

I have converted a standard MS access query to a stored procedure but am experiencing diabolical performance.

why is this SP running so badly?

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT dbo.vCaseCheckerMain.Documents_Override, dbo.vCaseCheckerMain.Case_ID, dbo.vCaseCheckerMain.Check_Type, dbo.vCaseCheckerMain.Flag_Date, dbo.vCaseCheckerMain.CNames, dbo.vCaseCheckerMain.Category, dbo.vCaseCheckerMain.Status, dbo.vCaseCheckerMain.CompanyName, dbo.vCaseCheckerMain.FirstName, dbo.vCaseCheckerMain.LastName, dbo.vCaseCheckerMain.[Compliance Officer], dbo.vCaseCheckerMain.Rating, dbo.vCaseCheckerMain.Due_Date, dbo.vCaseCheckerMain.Final_Rating, dbo.vCaseCheckerMain.Assigned, dbo.vCaseCheckerMain.Reason, dbo.vCaseCheckerMain.Documents, dbo.vCaseCheckerMain.Override_By, dbo.vCaseCheckerMain.Override_Reason

FROM dbo.vCaseCheckerMain
GROUP BY dbo.vCaseCheckerMain.Documents_Override, dbo.vCaseCheckerMain.Case_ID, dbo.vCaseCheckerMain.Check_Type, dbo.vCaseCheckerMain.Flag_Date, dbo.vCaseCheckerMain.CNames, dbo.vCaseCheckerMain.Category, dbo.vCaseCheckerMain.Status, dbo.vCaseCheckerMain.CompanyName, dbo.vCaseCheckerMain.FirstName, dbo.vCaseCheckerMain.LastName, dbo.vCaseCheckerMain.[Compliance Officer], dbo.vCaseCheckerMain.Rating, dbo.vCaseCheckerMain.Due_Date, dbo.vCaseCheckerMain.Final_Rating, dbo.vCaseCheckerMain.Assigned, dbo.vCaseCheckerMain.Reason, dbo.vCaseCheckerMain.Documents, dbo.vCaseCheckerMain.Override_By, dbo.vCaseCheckerMain.Override_Reason
HAVING (((dbo.vCaseCheckerMain.Rating) Is Null Or (dbo.vCaseCheckerMain.Rating)<>1))
ORDER BY dbo.vCaseCheckerMain.Documents_Override DESC , dbo.vCaseCheckerMain.Case_ID;
END

Thanks
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Well it returns

Workgroup Edition

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

When was the last time you reindexed your data or updated the statistics?

Can you tell I'm starting to grasp at straws? There doesn't appear to be anything obviously wrong with your query, so I cannot tell what the problem is. I think this is one of those situations where I would need access to the data to figure this one out.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

How would i go about re-indexing my data?

Can you tell I don't know much about SQL maintenance, never had any training, and the boss thinks this stuff runs itself!

I don't create indexes either, which i think is something i need to look into.

How do I know what columns should be indexed and how do I set up a maintenace plan to re-build them?

The in-house server has less than 1GB of disk space left , VSS is moaning it hasn't got enough space to perform the backups and I've had to decrease the swap file to 512mb just so the server doesn't fall over.

Well pay peanuts and all that!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Have you ever run SQLCop? If not, this would be a great time to. I encourage you to click the SQLCop link in my signature. Download the application and run it against your database. You do not need to run this application on your server, but you should log in to your DB.

Once SQLCop is running, expand indexes, and then click on Fragmented Indexes. SQLCop will examine all your indexes and tell you which ones are fragmented. You will likely see a list of indexes below Fragmented Indexes. Clicking on each index will show you the code you can use to defragment them.

Once you have defragmented all of your indexes, I would encourage you to expand "Missing Foreign Key Indexes". It's possible that you will see a lot of problems there too. Adding missing indexes will likely increase the size of the database, but also improve performance.

I wrote SQLCop. It is free to download and free to use (donations are appreciated but not necessary ).

The next thing I suggest you do is to search for duplicate indexes. Duplicate indexes are particularly bad because they add overhead to insert, updates, and deletes, and they take up extra room in the database.

Basically, a duplicate index is...

You can have multiple indexes on each table. Each index can have a single column or multiple columns in the index. SQL Server will allow you to make exact duplicates of your indexes. Some indexes may be different but are still duplicates. For example, suppose you have a people table with PersonId, Name, ShoeSize, EyeColor. PersonId is likely to be the primary key, so there will be an index for it. But, suppose you have an index on (Name and ShoeSize) and another index only on (Name). The index on name isn't needed because it also appears as the first column in the (Name, ShoeSize) index. There are subtle reasons why having duplicate indexes like this is OK, but it really only applies to multi-gigabyte databases that get a lot of traffic. If your DB was like this, for sure you would have a proper DBA.

Can you tell me how large your database is?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Have you tried this? How are things going?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jrbarnett]

(with apologies for cross posting) Putting together your postings in both the Access and SQL Server fora I am starting to put together a plan of action.

Do you have any servers in your offices that you could host this database on (whether they host SQL server yet or not)? Advantages to sell this to your boss:
1. improve performance (as data wouldn't need to be transferred across the VPN line) when system uses it.
2. allow you to have a higher spec server than you currently get, to get around the limited resources issue.
3. it gives you full control over the server, but as a negative, responsibility for maintaining the server itself, plus backups etc.
Cost is purchase of the server itself + accessories (Windows operating system license and SQL Server license + CALs, UPS etc), then time in migrating the data and modifying application to use local copy rather than remote, and configuring backups, maintenance jobs etc.

You could use the current VPN site as an offsite backup, or get quotes from them for a more powerful specification server and bigger capacity disks (and bandwidth). One or other will be needed. Quotes from alternative hosts should also be considered at this stage.

Your boss probably won't like this as it involves spending money, but the upshot is that if nothing is done, sooner rather than later, the server, and therefore anything that relies on it will fall over. You may have some sort of idea how quickly extra data gets added, and therefore approximate how quickly the disk will fill up (remembering to account for disk usage of backups etc as well as the database itself).

There are things you can do that may free up disk space on the server (they may not be possible depending how vigilant you are) including:
* delete unnecessary files (eg windows and SQL Server service pack install files, backups of windows updates and service packs that have been installed long ago), old contents of temp folders etc
* archive any historical data you don't need available immediately to a separate database you can host locally with SQL server express edition (free) but that has a limit of max 5 concurrent connections

John

 

[1DMF]

Just wanted to let you guys know I hadn't abandoned this thread.

Being the IT department, I get pulled in all directions and am for ever juggling multiple projects at once.

I have used what I have learned on another project where I have removed the binding direct to a table or query and used a passthrough query to run an SP and then used a wrapper class I wrote for basic filter manipulation of a DAO.recordset object and dynamically filter and bind that to the form.

It is working like a charm and the remote users are blown away with how fast the app runs (as am I), so i know i'm on the right track with this.

I just need the time to refactor the DB to use SP's and DAO.recordsets and hopefully will see a dramatic improvement.

I just wanted to thank you all for helping and didn't want you to think your time was wasted by what appears to me abandoning this thread!

I haven't , I'm just so damn busy all the time and pulled in too many directions to focus on this task at present!




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta