When I have database issues I usually run a SQL profiler for a couple of minutes (time needed to run the profiler depends on the traffic of the website of course) Make sure to log the profiler data into a table within SQL Server.
Now you can run a query on that table ordered by execution time. So you can see if certain queries take a lot of time and also if a particular query is executed a lot of times.
Now you can copy the query and run it within SQL Server Management studio. Make sure to check the option for "include actual execution plan" After the query has finished you can check the execution plan for "index scans" or "table scans" you don't want these. You want to have "seeks"
If scans occur check out the fields which are used in the "where" part of the query. Probably you need to create extra indexes for these fields.
You could also look at server performance of the database server of course and/or the webserver. Does the application recycle a lot? does it use a lot of memory on the webserver? what's the processor load on your database server? Do you run maintenance plans on your database server to keep indexes up to date? Did you import lots of new data into your database? Did you disable caching? I would check all this to isolate your problem.
The actual problem it turns out is what appears to be a simple datatype that in some way is thrashing the database, I suspect through some kind of misguided recursion. Taking out datatypes one by one from the doctype identified the problem. Without the naughty datatypes pages load sub second.
Maybe when you have worked it out ( I think your right it sounds like some sort of recursion ) maybe you could write a SQL statement that can find such issues and post it to this thread for others who receive this iregularity.
It was a hand crafted data type which we have since rewritten three times to speed it up, but with no success. Using the Umbraco log it appears to take no time at all to init and render and yet including two of them within a document type makes everything soooo slow!
I forgot to post an update. Data types are made up of three components, the DataType itself and then the Developers editor view (Prevalue Editor) to help set up the configuration for the datatype and the Editor view for web editors to see.
When the editor loads it needs to know the configuration of the Prevalues, i.e. the configuration set for the datatype.
Convention has it that in order to get the prevalues you create an instance of the prevalue editor and then get the configuration from that. Well in our prevalue editor, you had to choose a content node (from which child nodes would be presented in the editor). Thus when editing the content the Editor would load the PreValue editor to get the config. The Prevalue editor would needlessly create a list of every content item in Umbraco as it created and then threw away its interface just to get the config items.
Obviously I've now split this up a little more so that I have a separate configuration class that is part of the DataType rather than being in the PreValue editor.
I hope that this makes sense to someone, but it speeded up the datatype no end - silly mistake really.
Database incredibly slow - please help!
We run lots of umbraco projects and just one is amazingly slow. It is on a server with about 20 other projects, all of which are fine.
To load an Umbraco page in the backend takes around 20-30 seconds, whereas other sites load up in around 1 second, if not less.
Running SQL Server Profiler it returns around 84,000 rows. Not all represent a SQL Statement, but a lot do, so it seems to be an incredible amount.
We have upgraded to 4.0.2.1.
We are just not sure where to go from here. Does anyone have any advice on:
- what settings may have been set on this particular database to make it so slow?
- if it could perhaps be something else (the public website is fine and fast, so that's why we believe it to be the database)
- what to look for in the SQL Profile and what to do about it when we find it
- Anything else we should look at.
The document types are not complicated with around 20 items at most, all of which are fairly simple standard data types.
Please help as we are completely stumped.
Cheers
Steve
Hi Steve,
Have you tried re-indexing the database tables?
What version did you upgrade from?
Have you tried running "fiddler tool" on your browser to see what component takes its time loading?
Also what datatypes and how many do you have on the pages taking the longest and are they all default or is there some custom ones?
Tom
When I have database issues I usually run a SQL profiler for a couple of minutes (time needed to run the profiler depends on the traffic of the website of course) Make sure to log the profiler data into a table within SQL Server.
Now you can run a query on that table ordered by execution time. So you can see if certain queries take a lot of time and also if a particular query is executed a lot of times.
Now you can copy the query and run it within SQL Server Management studio. Make sure to check the option for "include actual execution plan" After the query has finished you can check the execution plan for "index scans" or "table scans" you don't want these. You want to have "seeks"
If scans occur check out the fields which are used in the "where" part of the query. Probably you need to create extra indexes for these fields.
You could also look at server performance of the database server of course and/or the webserver. Does the application recycle a lot? does it use a lot of memory on the webserver? what's the processor load on your database server? Do you run maintenance plans on your database server to keep indexes up to date? Did you import lots of new data into your database? Did you disable caching? I would check all this to isolate your problem.
Hi guys,
Thanks for the responses.
The actual problem it turns out is what appears to be a simple datatype that in some way is thrashing the database, I suspect through some kind of misguided recursion. Taking out datatypes one by one from the doctype identified the problem. Without the naughty datatypes pages load sub second.
So the next stage now is to debug the datatypes.
Thanks for your help
Cheers
Steve
Hi Steve,
Maybe when you have worked it out ( I think your right it sounds like some sort of recursion ) maybe you could write a SQL statement that can find such issues and post it to this thread for others who receive this iregularity.
Cheers,
Chris
Steven, it would be interresting to hear which datatype it was, if it is a standard umbraco datatype?
Hi all,
It was a hand crafted data type which we have since rewritten three times to speed it up, but with no success. Using the Umbraco log it appears to take no time at all to init and render and yet including two of them within a document type makes everything soooo slow!
I'll keep digging, but thanks for the comments.
Cheers
Steve
Sorry,
I forgot to post an update. Data types are made up of three components, the DataType itself and then the Developers editor view (Prevalue Editor) to help set up the configuration for the datatype and the Editor view for web editors to see.
When the editor loads it needs to know the configuration of the Prevalues, i.e. the configuration set for the datatype.
Convention has it that in order to get the prevalues you create an instance of the prevalue editor and then get the configuration from that. Well in our prevalue editor, you had to choose a content node (from which child nodes would be presented in the editor). Thus when editing the content the Editor would load the PreValue editor to get the config. The Prevalue editor would needlessly create a list of every content item in Umbraco as it created and then threw away its interface just to get the config items.
Obviously I've now split this up a little more so that I have a separate configuration class that is part of the DataType rather than being in the PreValue editor.
I hope that this makes sense to someone, but it speeded up the datatype no end - silly mistake really.
Cheers
Steve
is working on a reply...