Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bluedolphin
Starting Member
24 Posts |
 Posted - 2006-07-26 : 12:49:02
|
| Hello All:We are having a performance issue. I believe the box is too small for the workload we have. The box has 2 processors, 3 gig memory, and unfortunately is running reporting services as well as SQL server, therefore no dedicated box for SQL. One of the developers came to me today and informed that a table that we just released a report to was taking 6 minutes to return. I stripped out all joins to it, and it takes 1 minute 6 secods simply to return the first column for 8.5 million records. We took the query to a copy of the DB that lives on a box with 4 processors and the time cut in 1/2 to 31 seconds. I believe it is a no brainer that we need more processors, memory, CPU, and to get reporting services onto its own dedicated box so that SQL is not fighting for resources with Reporting Services... (But I am not the box person... I am a developer.) The database is almost 6 GIG at this point.2 things...1. Can anyone advise us on the resources we need for a database this size that will scale as we grow... we will have over 4,000 users.2. More resources or a new server is not a solution for today. Our indexes look good. (Scan Density [Best Count:Actual Count].......: 99.83%) for showcontig against this table. We plan on pointing the report, just for the time being to the box with 4 processors, but that is not our SQL server, so we cannot stay there. Besides, the data is one day old at that location, and we want to be real time. Is there anything we can do on our box today to try to improve performance while we order and get a new server... Is there something that we are not looking at that possibly could help us like paging/broken pages ... etc... any ideas?Thanks Tons for any help and input on the matter.BC-T |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-26 : 14:29:29
|
| Well Let's start with where you are and we'll go from there.1. How big is your database in GB.2. Please describe your current disk configuration (how many disks, what size, what RAID config, etc).Remember, generally SQL server needs RAM, DISK, CPU resources in that order (sometimes DISK, RAM, CPU depending on the size of your database).Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 15:00:42
|
quote:
Originally posted by bluedolphin
Hello All:We are having a performance issue. I believe the box is too small for the workload we have. The box has 2 processors, 3 gig memory, and unfortunately is running reporting services as well as SQL server, therefore no dedicated box for SQL. One of the developers came to me today and informed that a table that we just released a report to was taking 6 minutes to return. I stripped out all joins to it, and it takes 1 minute 6 secods simply to return the first column for 8.5 million records. We took the query to a copy of the DB that lives on a box with 4 processors and the time cut in 1/2 to 31 seconds. I believe it is a no brainer that we need more processors, memory, CPU, and to get reporting services onto its own dedicated box so that SQL is not fighting for resources with Reporting Services... (But I am not the box person... I am a developer.) The database is almost 6 GIG at this point.2 things...1. Can anyone advise us on the resources we need for a database this size that will scale as we grow... we will have over 4,000 users.2. More resources or a new server is not a solution for today. Our indexes look good. (Scan Density [Best Count:Actual Count].......: 99.83%) for showcontig against this table. We plan on pointing the report, just for the time being to the box with 4 processors, but that is not our SQL server, so we cannot stay there. Besides, the data is one day old at that location, and we want to be real time. Is there anything we can do on our box today to try to improve performance while we order and get a new server... Is there something that we are not looking at that possibly could help us like paging/broken pages ... etc... any ideas?Thanks Tons for any help and input on the matter.BC-T
this is why you need a DBA  . I don't believe that you are approaching this performance problem in the correct way. It is possible that you are correct in your assumption, but you have not ruled out the other (easier to implement) possibilities first.The speed difference between the two boxes could be because one box has a better disk subsystem or more available RAM for buffer cache so the full tablescan runs slightly faster. If you could, post the DDL for the table in question (include all indexes). Also, paste the query you are running as well. It would also be helpful to SET STATISTICS IO ON and SET STATISTICS TIME ON and then run the query from query analyzer. paste the results from that too.This will help us determine if you have a proper index in place for this particular query.-ec |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 15:17:07
|
| No problem...Please describe your current disk configuration...how many disks - 3what size - 136GB what RAID config - Raid 5 partitions - C 20Gb and D 250Gb partitionsRAM - 4Gb CPU - dual Intel Xeon 3.2 MHzThanks,BC-T |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 15:29:43
|
| Sure for the second request too...We had a clustered index on CSAP_School_List_ID as well with littel performance change.As I mentioned, we have stripped out all joins from the original query and are querying as follows and getting 8.5 million rows back in a little over 1 minuteSelect CSAP_School_List_IDfrom CSAP_School_List_Tif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CSAP_School_List_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[CSAP_School_List_T]GOCREATE TABLE [dbo].[CSAP_School_List_T] ( [CSAP_School_List_ID] [int] IDENTITY (1, 1) NOT NULL , [Test_ID] [int] NULL , [Report_Type_ID] [int] NULL , [Test_Standard_ID] [int] NULL , [Area_ID] [int] NULL , [Area_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Area_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [School_ID] [int] NULL , [SchName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Range_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Agg_ID] [int] NULL , [Num] [int] NULL , [Per] [float] NULL , [Yr_Diff] [float] NULL , [Yr_Sig_Diff] [float] NULL , [Ast_Sig_Diff] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Prof_ID] [int] NULL ) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_Test_ID] ON [dbo].[CSAP_School_List_T]([Test_ID]) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_School_Report_Type] ON [dbo].[CSAP_School_List_T]([Report_Type_ID]) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_School_Area_ID] ON [dbo].[CSAP_School_List_T]([Area_ID]) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_School_ID] ON [dbo].[CSAP_School_List_T]([CSAP_School_List_ID], [School_ID]) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_School_Range_ID] ON [dbo].[CSAP_School_List_T]([Range_ID]) ON [PRIMARY]GO CREATE INDEX [IX_CSAP_School_Agg_ID] ON [dbo].[CSAP_School_List_T]([Agg_ID]) ON [PRIMARY]GO |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-26 : 15:34:20
|
| Ok, 6GB database on a machine like that should be fine. How much RAM do you have allocated to SQL server? 2GB? 3GB?If I were to guess I'd say you have a bad query, bad indexes, or a bad database design. Like EC says, can you post some DDL and DML for the queries that are giving you issues? That will help us point you in the right direction.That box for 4000 users isn't nearly enough, but it should be enough to handle a 6GB database. Really where you need to focus dollars is on your disks. You need to get your TXLogs on a RAID 1 or RAID 0/1 array. For your data drives, you need more spindles. RAID 5 will probably be OK, but you'll need more spindles for sure. How many will depend on how much IO you need and how big that database is going to get.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-26 : 15:40:00
|
| You are actually returning 8.5 millions rows for something? Is there any way to return less data?Without a where clause, you are basically going to do a clustered index scan. No way to make that any faster really.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 15:41:27
|
| More info...Our network admin just added more RAM to our box so performance has improved, but we are still looking for more options we can implement today...dbcc showconfig (CSAP_School_List_T)SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.DBCC SHOWCONTIG scanning 'CSAP_School_List_T' table...Table: 'CSAP_School_List_T' (527340943); index ID: 0, database ID: 9TABLE level scan performed.- Pages Scanned................................: 95271- Extents Scanned..............................: 11929- Extent Switches..............................: 11928- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.83% [11909:11929]- Extent Scan Fragmentation ...................: 11.94%- Avg. Bytes Free per Page.....................: 65.4- Avg. Page Density (full).....................: 99.19%DBCC execution completed. If DBCC printed error messages, contact your system administrator.SQL Server Execution Times: CPU time = 781 ms, elapsed time = 14867 ms. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 15:44:08
|
quote:
Originally posted by bluedolphinWe had a clustered index on CSAP_School_List_ID as well with littel performance change.As I mentioned, we have stripped out all joins from the original query and are querying as follows and getting 8.5 million rows back in a little over 1 minuteSelect CSAP_School_List_IDfrom CSAP_School_List_T
ok, you should probably have a clustered index on this table. We can discuss that issue later, becuase deciding which column(s) to put th eclustered index on can sometimes be tricky.as for your query, where are you running it from? Are you running your query from a query analyzer session running on your server or are you running it from a workstation over the LAN?-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 15:49:01
|
| Michael's point about returning 8.5 million rows is a good one. Maybe we should be working with your original query (joins and all). Your query right now is just a measure of disk performance and read ahead buffer. Make sure you run your query with SET STATISTICS IO ON and SET STATISTICS TIME ON enabled. that will give us some i/o and time related statistics.-ec |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 15:59:30
|
| The query in reporting services is not returning 8.5 million rows. It is returning a much smaller recordset against the table which has 8.5 million rows in it. It takes 6 minutes to render the report against a query based on this table.We are joining to Test_ID, Report_Type_ID, Test_Standard_ID, Range_ID, Agg_ID, and Prof_ID.The tables that are joined to are very small tables (1-20 records). The biggest one is Test_Standard_T which has only 2000 rows in it.So we stripped out all joins and still can't seem to get the performance that we want out of this table.In reporting services the query that I described above was returning 80 records with the where clause. It was taking 6 minutes. Now that we have more RAM, it is taking 4 minutes to return the same 80 records, some improvement... but we need better.I still stand by my conclusion that "We need a bigger boat..." But I'm scrambling for something I can implement before the box gets here and is configured. A report that takes 4 minutes to return 80 records will make our users and management very antsy in the meantime.Thanks,BC-T |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 16:02:40
|
| I ran SET STATISTICS IO ON goSET STATISTICS TIME ONgoSelect CSAP_School_List_IDfrom CSAP_School_List_Tand it simply returned to me the recordset with no other information... I'm not sure what I'm doing wrong on this one.BC-T |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-26 : 16:03:45
|
| Ok, in that case, show us the reporting query, the one that now takes 4 minutes for 80 records. I bet with the right indexes we can make it much faster than that.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 16:07:26
|
quote:
Originally posted by bluedolphinIn reporting services the query that I described above was returning 80 records with the where clause.
Ok, what is in your WHERE clause?-ec |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 16:24:08
|
| as for your query, where are you running it from? Are you running your query from a query analyzer session running on your server or are you running it from a workstation over the LAN?We run it in query analyzer and from an asp.net, our front-end, with the same slow results. Currently,... and unfortunately, we have SQL Server, Reporting Services and asp.net all on the same box. I think that this is a huge part of our problem. Our plan is to move reporting services onto its own box, but asp.NET will stay where it is for now. I realize that this is not a good model. But as most of you know, sometimes the perfect config is not possible at first, but management still wants answers as to why we are having problems and how it can be fixed now... Of course they don't want to here that that there are too many things pounding the limited resources available. I need either some kind of solution, or a clear message to take back to them to say that there is little hope for us until we move to a more robust enterprise model. You know management, they can drag their feet and make you move forward on things despite your best advise. This is where we are today I think. End users generally don't care about boxes and configurations until you are close to being on your knees like today,... then they come to you looking for answers... when they were warned up front that performance would be affacted if we crammed all of this onto one server. If there is a quick stop gap for me today that you can think of... great. Otherwise, please give me some ammo and the backup I need to convince management that it's a resource issue. They don't seem to believe my analysis without confirmation from other sources. This is because more servers mean more $.... and if they can get by with what they have, then they will. My managemnet is not an IT department. I am more or less managed by my end-users, who don't think boxes and system configs until its too late. They just plow forward and sometimes make you do things against your will. (Enough Rant)As for the clustered index, It originally had a clustered index on the primary key. We remove it just to see what happened. No change... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 16:27:12
|
quote:
Originally posted by bluedolphin
as for your query, where are you running it from? Are you running your query from a query analyzer session running on your server or are you running it from a workstation over the LAN?We run it in query analyzer and from an asp.net, our front-end, with the same slow results. Currently,... and unfortunately, we have SQL Server, Reporting Services and asp.net all on the same box. I think that this is a huge part of our problem. Our plan is to move reporting services onto its own box, but asp.NET will stay where it is for now. I realize that this is not a good model. But as most of you know, sometimes the perfect config is not possible at first, but management still wants answers as to why we are having problems and how it can be fixed now... Of course they don't want to here that that there are too many things pounding the limited resources available. I need either some kind of solution, or a clear message to take back to them to say that there is little hope for us until we move to a more robust enterprise model. You know management, they can drag their feet and make you move forward on things despite your best advise. This is where we are today I think. End users generally don't care about boxes and configurations until you are close to being on your knees like today,... then they come to you looking for answers... when they were warned up front that performance would be affacted if we crammed all of this onto one server. If there is a quick stop gap for me today that you can think of... great. Otherwise, please give me some ammo and the backup I need to convince management that it's a resource issue. They don't seem to believe my analysis without confirmation from other sources. This is because more servers mean more $.... and if they can get by with what they have, then they will. My managemnet is not an IT department. I am more or less managed by my end-users, who don't think boxes and system configs until its too late. They just plow forward and sometimes make you do things against your will. (Enough Rant)As for the clustered index, It originally had a clustered index on the primary key. We remove it just to see what happened. No change...
ok got it. What is your WHERE clause that is used in your slow query?-ec |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 16:30:26
|
| Where clause ...where test_ID = 123 and Report_Type_ID=2 and School_ID = 123 and Prof_ID IS NOT NULLEvery foreign key that I talk about joining to is a non-clustered index in the smaller table.I would love to post the whole query, but management is afraid I am already giving out too much information and is getting nervous.Thanks,BC-T |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-26 : 16:32:41
|
| Yep, show us the WHERE clause or the entire "slow running reporting query" and we can probably tell you how to make the go faster with your existing hardware. I feel confident that between EC and I we can solve your imeediate need.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 16:44:53
|
quote:
Originally posted by bluedolphin
Where clause ...where test_ID = 123 and Report_Type_ID=2 and School_ID = 123 and Prof_ID IS NOT NULL
OK, I take it that none of these columns are particularly selective. what about Prof_ID, what is the cardinality of that column? If none of these columns are that selective, you may have to implement a composite index. Indexes need to be selective, otherwise they are useless and just cause performance problems. What is probably happening is that SQL Server is grabbing one of your not so selective indexes and then jumping over to the table to fetch the row. It will jump back and forth like this (this is called a bookmark lookup) until all the rows are fetched that you selected. It is also possible that you are doing a full table scan on your main table. Run your full query with the execution plan enabled and see what index it is using or if it is doing a full table scan.quote:
Every foreign key that I talk about joining to is a non-clustered index in the smaller table.
good, it is always a good idea to index the foreign keys.quote:
I would love to post the whole query, but management is afraid I am already giving out too much information and is getting nervous.
no worries. I'm almost complete with my open source clone of your software.  -ec |
|
|
|
bluedolphin
Starting Member
24 Posts |
Posted - 2006-07-26 : 16:46:35
|
| The original developer has refused to give me the entire query. I guess they think that by putting all of this out there, we are giving a window to our world and they are uncomfortable with that.I have had excellent luck in this community finding the answers I need. But usually I'm sitting here hacking at it alone. Once I find the answer, I guess I never share with them that I got the answer from posting on this site. When they were having problems this morning, I walked over and offered to help, suggesting that I know a great forum that is an excellent resource and that maybe I could dig around here to see what Ican find out. Now that they know that sometimes I have to post detailed info like this, they clearly are not comfortable with this practice... even the other developer is not comfortable with it. I don't want to burn my bridges and have them refuse to let me post on sites like this. Online forums are often excellent resources that I make frequent use of, so I need to tread lightly so I don't lose the priviledge. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-26 : 16:53:03
|
| I noticed that you used the FLOAT datatype in your table. You should be aware that a FLOAT (REAL is a synonym of FLOAT btw) is not very precise. If you need decimal precision use the DECIMAL or NUMERIC datatypes instead.FLOATS should be used for values where exact precision is not important (really large numbers, scientific notation etc.) Usually, in a database this is not what you want.Check BOL for datails regarding these datatypes.-ec |
|
|
|
Next Page
|
|
|
|
|
Performance Question