| Author |
Topic |
|
Temrael
Starting Member
14 Posts |
 Posted - 2007-04-03 : 11:52:56
|
| Hi,I posted last year when we were having problems with a new SQL box we had moved to here...[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74650[/url]...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.If this post breaks any rules please let me know.Cheers,Tem |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-03 : 12:12:37
|
| I'm sure someone will be able to help you out for a fee, but let's start with some of the easy free stuff.Run SQL Profiler. Launch it from a client machine, point it to your SQL Server, save the data to a table, probably just accept the default events (I modify it a bit, but for now just keep the default events). Run it for a few hours during the busy times of the day. Stop the trace, then run this in Query Analyzer (2000) or Management Studio (2005):SELECT TOP 1000 Duration, TextDataFROM YourTraceTableNameWHERE TextData IS NOT NULLORDER BY Duration DESCThis will show you the worst performing queries. You need to weigh this list against how often the query runs though. Also ORDER BY CPU (or maybe it's CPUTime, not in front of trace right now). Once you have the queries you need to look at, post them here along with the DDL for your tables and indexes. DDL is the CREATE TABLE, CREATE INDEX, and ALTER TABLE statements. We only need the DDL for the tables involved in the queries you want us to look at.Is your system SQL Server 2000 or SQL Server 2005? This makes a difference when reading the Duration data from the trace table.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-03 : 13:47:44
|
quote:
Originally posted by tkizer
I'm sure someone will be able to help you out for a fee, but let's start with some of the easy free stuff.Run SQL Profiler. Launch it from a client machine, point it to your SQL Server, save the data to a table, probably just accept the default events (I modify it a bit, but for now just keep the default events). Run it for a few hours during the busy times of the day. Stop the trace, then run this in Query Analyzer (2000) or Management Studio (2005):SELECT TOP 1000 Duration, TextDataFROM YourTraceTableNameWHERE TextData IS NOT NULLORDER BY Duration DESCThis will show you the worst performing queries. You need to weigh this list against how often the query runs though. Also ORDER BY CPU (or maybe it's CPUTime, not in front of trace right now). Once you have the queries you need to look at, post them here along with the DDL for your tables and indexes. DDL is the CREATE TABLE, CREATE INDEX, and ALTER TABLE statements. We only need the DDL for the tables involved in the queries you want us to look at.Is your system SQL Server 2000 or SQL Server 2005? This makes a difference when reading the Duration data from the trace table.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
One additional thing you can do is to insert the highest CPU queries into a table, and then run the Index Tuning Wizard against that table.I usually capture the workload from three different days, get the 500 highest CPU queries, and run the Index Tuning Wizard against each day’s workload. If each run of the Index Tuning Wizard suggests the exact same index, then create that index, and repeat the process of capturing workload with Profiler, running the Index Tuning Wizard, and creating indexes.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-03 : 14:03:17
|
The lot in the USA are obviously happy to work for free, but if you want a friendly Brit face I can help you for several thousand spondulix a day  The last ColdFusion system I worked on had a bunch of "SELECT * FROM ..." in the CF code, and then they had added some TEXT datatype columns to the tables, and once folk started filling the TEXT columns with loads of juicy data performance fell like a stone ...... please tell me you haven't inherited that database?! Kristen |
|
|
|
Temrael
Starting Member
14 Posts |
Posted - 2007-04-03 : 15:47:07
|
| Thanks a lot for all the pointers guys/gals.Tara - Thanks for that, I'd assumed using Profiler was harder than that heh. ;o)I've just had a quick look (it's evening now but I wanted to see if I could follow your instructions) and I can see what looks like some fairly slow running queries. In fact it is a stored procedure that is called from a pretty heavily used page, so I'll have a chat with the guy that wrote that tomorrow as I'm suprised it is the slowest running one (I think it looks for the oldest entry in a table of "emails" every 15 seconds while a page is open).To answer your question, it's a SQL 2000 box. Is it a bad idea to run profiler and query analyser via Terminal Services on the box, I don't have a client with the tools on?Will the fact profiler is running for a few hours when we are busy have much of an impact on the performance of the server while it gets on with it's day job? In the end we need the data so we will have to take whatever the hit is in terms of performance I just wonder what I should expect?Kristen - Define several thousand spondulix hehe. ;o)I believe we have a few SELECT *'s kicking about and a fair amount of TEXT columns too. ;o)I've not really inherited this per se, I've worked for the company for a couple of years but I'm only beginning to get a handle on the server side of the website recently.Cheers,Tem |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-03 : 16:19:56
|
quote:
Originally posted by Temrael
Is it a bad idea to run profiler and query analyser via Terminal Services on the box, I don't have a client with the tools on?
Yes that's a bad idea. SQL Profiler is a bit of a hog. So that you don't impact your data collection, you want to run the tool on a different machine. Are you able to install it somewhere else? If you can't, then I'd watch performance on the database server while it is running. quote:
Originally posted by Temrael
Will the fact profiler is running for a few hours when we are busy have much of an impact on the performance of the server while it gets on with it's day job? In the end we need the data so we will have to take whatever the hit is in terms of performance I just wonder what I should expect?
Yes it can impact your users. I've run it for 30 minutes and severely impacted my users. But we had to collect the data to see where the issues were. You've got to weigh the data collection against user expectations. Most environments aren't imapcted by SQL Profiler running though.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-04 : 01:14:33
|
| "Define several thousand spondulix"It basically involves you getting lots of free beer!"In fact it is a stored procedure that is called from a pretty heavily used page"Might be worth "recompiling" that Sproc just in case it has got a rubbish (or more probably "now-stale") query plan cached. That will cause that Sproc to have a new query plan, next time it runs, which will take into account any new Statistics that have been updated on the tables etc.sp_recompile 'MySproc'"I believe we have a few SELECT *'s kicking about and a fair amount of TEXT columns too."Have a look at the offending Sproc to see if any of the tables it uses have had new columns added (and if it uses SELECT *). That's one of the main problems with a "SELECT *" - its a Time Bomb for the future when the shape of the DB changes and columns being retrieved are not used by the application; couple that with "columns not being used by the application are TEXT" and that will kill performance in a rush!Kristen |
|
|
|
Temrael
Starting Member
14 Posts |
Posted - 2007-04-04 : 09:19:25
|
| Thanks again.I ran profiler again for 15 mins or so this morning and got very different looking results. The "biggies" from last nights trace are mere chatter compared to some of the looong queries happening now. For example...SELECT dealership_username dealership_password dealership_id FROM mt_dealership WHERE dealership_username = 'something' AND dealership_password = 'something else'...had a duration of 10,686 (which I guess is 10.6 seconds)? Which seems pretty slow for what looks (to my ignorant eyes) like a very simple query?Kristen - I've sent you a mail re: the possibility of getting you to take a look for us. I hope that's ok?Cheers,Tem |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Temrael
Starting Member
14 Posts |
Posted - 2007-04-04 : 10:21:02
|
| How do I find that out Brett? Is it viewable somewhere under the table in Enterprise Manager?Thanks,Tem |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-04 : 11:00:02
|
"Is it viewable somewhere under the table in Enterprise Manager?"Right click the table : All tasks : Manage indexesOr Right click the table : Design : Use the "Manage indexes Icon" (penultimate) then use the DropDown to "view" each index.You can also do (e.g. in Query Analyser):-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ONSELECT dealership_username dealership_password dealership_id FROM mt_dealership WHERE dealership_username = 'something' AND dealership_password = 'something else'GOSET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SET SHOWPLAN_TEXT OFFGO Comment in one of the SET options at the top (can't do both at the same time).The SHOWPLAN will show you what indexes are being used etc.The STATISTICS will show you the Logical I/O operation (ignore the physical ones, that depends on what's in memory etc.). If you make changes (such as creating an Index) you can re-run the Statistics checks to see whether you have reduced the logical I/O = which in general terms relates to the amount of improvement you can expect.SCAN COUNTs are "bad" - so if you query has lots of them (usually because of multiple JOINs which have no useful indexes) then reducing them will help a lot.Kristen |
|
|
|
Temrael
Starting Member
14 Posts |
Posted - 2007-04-04 : 15:17:51
|
| Thanks again guys. :o)I've had a look and that table has one index called "PK_mt_dealership" that seems to just index the primary key for the table (the dealership_id column).As I understand it that table is a bit too big and too many functions and bits of our site access it so I guess it either needs to be heavily optimised/indexed or split into smaller tables to spread the load a bit.What about the indexing of this table should I be looking at?The ticked options (SQL 2000 remember) are...Unique Values = OnFill Factor = 90File Group = PrimaryCheers,Tem |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-04 : 15:20:24
|
| We would need to see the queries that hit this table in order to determine what to index. Show us the most common queries and the ones that are run semi-frequently.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-05 : 02:44:59
|
"What about the indexing of this table should I be looking at?"Basically if you JOIN to a second table then all the field, in both tables, which are part of the JOIN clause should be indexed.If you use a WHERE clause to limit a query then the fields in the WHERE clause should be indexed. (In the example that would be dealership_username and dealership_password.So ... pursuing that route you would index every column! Unfortunately life isn't like that:When you insert a record entries must be added to the indexes for all the columns which are indexed. If you update a record, such that a column present on an Index changes, that index must be updated.If you have gazillions of indexes SQL Server may take longer deciding HOW best to construct the resultset than actually getting the data!Then you need to decide whether to have composite indexes, or separate ones. You COULD, for example, have a composite index that contained both dealership_username and dealership_password columns.Having all those indexes is no use if SQL Server won;t actually use them. SQL Server decides on which index to use based on Statistics (so its important that you have maintenance jobs to keep them up to date) which tells it the "selectivity" of the data in the index. For an index to be used its values need to be reasonably unique. As a daft example consider an index on a Yes/No column. If you have 1,000,000 rows in your table and a 50:50 between Yes and No the index gets you nothing - its much faster to walk through the whole table rather than pick on row from t he Index, and then seek to the page in the table containing the row, and so on - the disk heads would be zooming around all over the disk surface!OTOH if 99.999% of your records are Yes, and the other 0.001% are No then an index would dramatically help any query that was looking for the "No" records. (That's where the statistics come in - SQL will use the index for a "No" query, and not for a "Yes" query).There there is a covered index. Say you do a lot of:SELECT Col1FROM MyTableWHERE Col2 = 'FOO'If you have a composite index on Col2, Col1 then SQL Server will use that and never go near the table itself - after all, Col1 is in the index so it can get the value from the index instead of the table. Very fast. Obviously this is NOT a good approach if your SELECT statement includes every column in the table Looking at your query:dealership_username = 'something'is that unique? Sound like ti ought to be (another use of an index is that it can enforce uniqueness)SELECT TOP 100 [RowCount] = COUNT(*), dealership_usernameFROM mt_dealershipGROUP BY dealership_usernameORDER BY [RowCount] DESC If all of the rows shown have a RowCount of 1 then its unique. Assuming this query is used frequently then you definitely need an index on that column:CREATE UNIQUE INDEX IX_dealership_username ON mt_dealership( dealership_username) "PK_mt_dealership primary key ... dealership_id column ... Fill Factor = 90"If dealership_id is an IDENTITY (i.e. you insert new rows with incrementing numbers, and NOT "random" numbers) then this would be somewhat better as 100%. Sadly SQL's Maintenance Plans will reset this to 90% by default, which may be how it got to be 90%  Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-05 : 09:46:35
|
quote:
Originally posted by X002548
Ya know, now we're just taking food out of some poor uk dba's mouth...
All the information Kristen posted is available from Books Online and other sources, so I'm sure the OP would have figured it out.CODO ERGO SUM |
|
|
|
Temrael
Starting Member
14 Posts |
Posted - 2007-04-05 : 15:35:40
|
Thanks again guys.It looks as though we'll be getting Kristen in to look at this properly so we're not robbing food from anyone.  Kristen - I've had a quick look and it would appear that the usernames and passwords are not unique currently, only the primary key (the dealer number) is. That sounds like it might be something we want to look at. Whilst the combinations of usernames and passwords are unique it seems an inefficient way of doing logins? It would be pretty usual and obvious to have and enforce unique usernames?Tem |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-08 : 02:33:01
|
| Duplicate usernames implies a bit of lack of control, but we use it where, for example, the UserName is an Email Address and multiple householders share an email address .... the damn real world getting in the way again!Also we find that clients who are importing data from various sources don't want to bother to spend time on cleanup, so they allow multiple re-registrations (or whatever) and only want uniqueness on UserName + Password.They then moan about the cost, and customer complaints, of sending out 5 catalogue to the same person!!So you are probably somewhere in that boat ... but having the "intention" of making UserName unique sounds about right to me.On the Index issue - is UserName is "almost unique" and index will still be useful. (The optimiser may make a different, better, choice for an index which is declared as Unique, because obviously the selectivity is 100%, but your index is going to be 100% for most of the data, and the statistics should enable the optimiser to make that choice - possibly it will cost a few extra CPU cycles in making the Choice)Kristen |
|
|
|
|
Getting a SQL expert to review SQL Server and DB?