Since upgrading to service pack 3a (from sql server with
no sp) I am experiencing performance issues with my full
text searches. Its hard to quantify the problem - but
since the upgrade I am definitely seeing significatnt
increase in duration for searches - especially when they
run concurrently.
As part of the upgrade the catalogs were rebuilt.
Any ideas would be appreciated
This is abnormal. There were some problems with the search algorithim prior
to SP 3 relating to ranking. If you are doing ordering you may experieince
some difference in performance.
I noticed a performance increase. How many simultaneous queries are you
running? You can get this metric by looking at the performance monitor
counter Microsoft Search:Queries
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
> Since upgrading to service pack 3a (from sql server with
> no sp) I am experiencing performance issues with my full
> text searches. Its hard to quantify the problem - but
> since the upgrade I am definitely seeing significatnt
> increase in duration for searches - especially when they
> run concurrently.
> As part of the upgrade the catalogs were rebuilt.
> Any ideas would be appreciated
|||Hilary,
Right now it is running at an average of about 300000, but
we are experiencing light load, and we are having no
problem. I'll keep the metric running and monitor what
happens when we do have problems.
The stored procedure that runs the various
containscontable commands uses dynamic sql, and also joins
on tables in other databases on the same server - do you
know if either of these aspects could have been affected
by SP3. I have made no code changes that would account
for the problems I am seeing.
THanks, Rob
>--Original Message--
>This is abnormal. There were some problems with the
search algorithim prior
>to SP 3 relating to ranking. If you are doing ordering
you may experieince
>some difference in performance.
>I noticed a performance increase. How many simultaneous
queries are you
>running? You can get this metric by looking at the
performance monitor
>counter Microsoft Search:Queries
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
>
>.
>
|||Hilary,
I just saw the cpu flat-lining again, but the metric did
not really change.
Rob
>--Original Message--
>This is abnormal. There were some problems with the
search algorithim prior
>to SP 3 relating to ranking. If you are doing ordering
you may experieince
>some difference in performance.
>I noticed a performance increase. How many simultaneous
queries are you
>running? You can get this metric by looking at the
performance monitor
>counter Microsoft Search:Queries
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
>
>.
>
|||are there any error messages in the event log from MSSearch or MSSCi?
When you say you saw the CPU flat lining I take it, that CPU is going to
100%, or it is remaining constant?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:333301c4aba7$988ef3d0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> I just saw the cpu flat-lining again, but the metric did
> not really change.
> Rob
> search algorithim prior
> you may experieince
> queries are you
> performance monitor
> message
|||There are no errors or warnings - and the searches do
return after several seconds, but they run the cpu at 100%
until they return, and are much slower than before SP3 was
installed.
Do you think the problem could be related to cross-
database queries or dynamic sql?
Rob
>--Original Message--
>are there any error messages in the event log from
MSSearch or MSSCi?
>When you say you saw the CPU flat lining I take it, that
CPU is going to[vbcol=seagreen]
>100%, or it is remaining constant?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:333301c4aba7$988ef3d0$a501280a@.phx.gbl...
with[vbcol=seagreen]
full[vbcol=seagreen]
but[vbcol=seagreen]
they
>
>.
>
|||dynamic sql is often inefficient, but not always as in some cases the
execution plan will be cached and reused.
However, in the type of queries you would use with SQL FTS it is not cached.
It could be related to cross database queries, especially if you are not
fully qualifying your database and owner names.
Can you post your query here? Also how many queries are occuring per second
or minute? And how many rows are in your table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:36bb01c4abca$99400670$a501280a@.phx.gbl...[vbcol=seagreen]
> There are no errors or warnings - and the searches do
> return after several seconds, but they run the cpu at 100%
> until they return, and are much slower than before SP3 was
> installed.
> Do you think the problem could be related to cross-
> database queries or dynamic sql?
> Rob
> MSSearch or MSSCi?
> CPU is going to
> with
> full
> but
> they
|||Rob,
There were several MAJOR fixes related to FTS in SQL Server 2000 SP3 that
caused existing FT Catalogs to be automatically rebuilt (if you selected
them during the SP3 installation) or your could of deferred the rebuild if
the FT Catalogs were large and use the procedures in KB article Q240867 at a
later point in time. Do you know which option you selected during the SP3
upgrade?
Below are two KB articles that document these fixes related to FTS in SP3:
327217 (Q327217) INF: Installing SQL Server 2000 Service Pack 3 Rebuilds All
Full-Text Catalogs
http://support.microsoft.com/default...b;en-us;327217
312410 (Q312410) "FIX: Full Text RANK Results May Vary After Catalog
Repopulation"
http://support.microsoft.com/default...b;en-us;312410 (references
Q240867)
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:36bb01c4abca$99400670$a501280a@.phx.gbl...[vbcol=seagreen]
> There are no errors or warnings - and the searches do
> return after several seconds, but they run the cpu at 100%
> until they return, and are much slower than before SP3 was
> installed.
> Do you think the problem could be related to cross-
> database queries or dynamic sql?
> Rob
> MSSearch or MSSCi?
> CPU is going to
> with
> full
> but
> they
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Tuesday, March 20, 2012
Wednesday, March 7, 2012
Possible SQL 2000 Performance Issue
We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events
-
> service account successful logon - we would expect this as we have the ful
l
> auditing enabled.
> However, the MOM application is reporting the following is part of an erro
r
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of thi
s
> - is it likely to be the amount of auditing that is causing the error or n
ot
> enough RAM or combination of the two ?
>
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events
-
> service account successful logon - we would expect this as we have the ful
l
> auditing enabled.
> However, the MOM application is reporting the following is part of an erro
r
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of thi
s
> - is it likely to be the amount of auditing that is causing the error or n
ot
> enough RAM or combination of the two ?
>
Possible SQL 2000 Performance Issue
We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?
Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events -
> service account successful logon - we would expect this as we have the full
> auditing enabled.
> However, the MOM application is reporting the following is part of an error
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
> - is it likely to be the amount of auditing that is causing the error or not
> enough RAM or combination of the two ?
>
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?
Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events -
> service account successful logon - we would expect this as we have the full
> auditing enabled.
> However, the MOM application is reporting the following is part of an error
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
> - is it likely to be the amount of auditing that is causing the error or not
> enough RAM or combination of the two ?
>
Possible SQL 2000 Performance Issue
We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events -
> service account successful logon - we would expect this as we have the full
> auditing enabled.
> However, the MOM application is reporting the following is part of an error
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
> - is it likely to be the amount of auditing that is causing the error or not
> enough RAM or combination of the two ?
>
running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
service account.
Our audit level is set to All - this is in accordance with the customer's
requirements.
The application log is constantly being written to with mssqlserver events -
service account successful logon - we would expect this as we have the full
auditing enabled.
However, the MOM application is reporting the following is part of an error
in the application log;
"Not enough storage is available to process this command"
The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
- is it likely to be the amount of auditing that is causing the error or not
enough RAM or combination of the two ?Any problem observed in the SQL server error log? Is ounds more like you are
running low/out of disk space.
"georgejuggins" wrote:
> We have a SQL 2000 SP3 Server hosting the MOM 2000 application. We are
> running the MSSQLSERVER service and SQLSERVERAGENT service using a Domain
> service account.
> Our audit level is set to All - this is in accordance with the customer's
> requirements.
> The application log is constantly being written to with mssqlserver events -
> service account successful logon - we would expect this as we have the full
> auditing enabled.
> However, the MOM application is reporting the following is part of an error
> in the application log;
> "Not enough storage is available to process this command"
> The server has 2GB RAM and the Sqlservr process runs at about 1.5Gb of this
> - is it likely to be the amount of auditing that is causing the error or not
> enough RAM or combination of the two ?
>
Saturday, February 25, 2012
Possible IO Problems
Hi guys
We have a 23GB SQL database and we are experiencing some pretty dire performance problems.
I think this may be related to the disk setup.
We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)
We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.
On a daily basis we are adding approximately 83,000 records.
Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.
Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.
In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.
We maintain our redundancy as both disks are mirrored and we also have offsite backups.
Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)
Many thanks for you help guys
HanleyOriginally posted by Hanley
Hi guys
We have a 23GB SQL database and we are experiencing some pretty dire performance problems.
I think this may be related to the disk setup.
We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)
We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.
On a daily basis we are adding approximately 83,000 records.
Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.
Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.
In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.
We maintain our redundancy as both disks are mirrored and we also have offsite backups.
Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)
Many thanks for you help guys
Hanley
Its been a while since I've had to beat on this one. So bear with me while I throw some questions at you.
What is the general setup of the server? CPU(s)? RAM?
How many SCSI controllers do you have?
How much memory do they have?
How fast are the SCSI drives?
Can you do replication of the database to another server for read only IIS?
RAID 5 is typically faster than RAID 1 in the real world. And if your choke point is the controller, throwing all the drives in the world at it isn't going to help.
In addition even splitting the disks for transaction logs and the datafiles is not going to give you much more speed if they are going against the same controller. As a matter of fact it may even slow you down.
If your C/D are on a separate controller from the E, then run your db files to c/d and your logs to e.
Just how I see it....|||on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.|||It also sounds like your OS is on a RAID 5. If the OS is a raid 5, then the swap file is probably there as well, so the system has to calcualte parity for the swap file space every time it writes to the virtual memory. This can hammer you performace really, really badly.
I would recomment installing a separate partition or disk, separate from the RAID arrays, at least any with parity, and point the vitual memory swap file to live there.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.
Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.
It depends on the SCSI controller and drives being optimized. If so the the three physical I/O's should be running concurrently (write/3 plus parity) vs. RAID 1 being paralell(sp?).
I grant speed diff of RAID 5 on 3 disk is questionable depending on HW, Firmware (FW), and SW vs RAID 1. Running RAID 5 on 5+ disks will generally show the difference.
Another point in RAID 5 favor though depending on OS/FW though is continued expandability. I think the high end SCSI size is about 320GB and you are talking big $ per. In RAID 1 you are limited to that, unless you do RAID 10. but then you might as well look at SAN/NAS. With a good RAID 5 you can add drives to the limit of the controllers.
But if he is trying to run those databases and transactions on a 2x500Mz with 2G RAM a FastSCSI 1 controller and a 10/100NIC, he might as well be trying to pump out of the Pacific using a hand bailing pump and a straw.|||Originally posted by craigmc
Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.
RAID 0 = Striping
RAID 1 = Mirroring
RAID 5 = Striping w/ partity
RAID 10 = Striping w or w/o parity and mirroring (generally with parity)|||craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.
also, it becomes a defacto standard for small to medium size business that do not utilize san technology, to put the data on raid-5 with 5+ drives spread through 2+ scsi controllers, logs on raid-1, also multiple controllers to allow for semi-parallel io. I also recommend to put tempdb on a separate controller, maybe even 1 or 2 non-raided physical disks. AND i appload your suggestion on the location of the swap file.|||and watch paging (total pages), cpu (total pct) and logical disk (avg disk queue length).
This will give you a good first approximation at whether you problem is not enough memory (too much paging), not enough processor (cpu utilization) or insufficient disk performance.
Microsoft recommends that paging be kept below 20 pages/sec.
I suggest if the CPU(s) are spending a lot of time above 80%, you might need faster or more processors.
But I suspect you're problem is disk performance. The thing that REALLY leaps out at me is you have both the transaction logs and data on the same drive. That's nearly always a bad idea, unless you are just too constrained by money to have it any other way. Probably nothing will speed up your response time more - particularly if you're doing a lot of data changes as opposed to mostly SELECTS - than separating the log from the data files.
No doubt, RAID 1 is faster than RAID 5. RAID 10, if you can afford it, is even better. Sometimes it's hard to convince people of this, but RAID 5 is SLOW to write data. So if you're doing a lot of INSERTS, UPDATES or DELETES, get off of a RAID 5 array, it's probably killing you.
ALL THAT SAID, keep in mind that throwing hardware at a performance problem is the last avenue you should persue. If you have performance issues, first TUNE YOUR SQL. One frequent but inefficient query can cost a lot of machine performance. Next, TUNE YOUR DATABASE. Do you have the proper indexes to make your queries execute quickly? Finally, TUNE THE ENGINE. SQL 2000 does a good job of this by itself, but there may be parms you can change to improve it even further. These three steps, particularly the first two, at least have the potential to give you far greater performance gains than any amount of hardware. Only after you've convinced yourself that the common queries are sensibly formuated and have the proper indexes should you even consider spending money on faster hardware. Better hardware probably won't gain you more than 2x to 10x your current speed, but tuning can literally get you gains of 100x - 10000x.
If necessary, use the Profiler to analyzer incoming queries. Take the frequent ones, paste them into Query Analyzer and take a look at the Query plans and I/O costs. Build (or add columns to) indexes to eliminate as many of the table and index scans as you can and get the engine doing index seeks. Time and time again I've seen a little work here yield eye-poping gains.|||Originally posted by ms_sql_dba
craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.
Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.|||Originally posted by craigmc
Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.
No problem. You are right about the swap file.
Until we know a little more about the server he's running on we're just shooting in the dark as to disk access being the problem.
And tuning can help, but some of us are really maintaining databases that are for applications delivered by vendors. We had one application delivered by a vendor that the EOD process had climbed north of 6 hours over time as the data increased. After complaining enough they finally delivered the next version which dropped it to 30 minutes with the same amount of data. They didn't change the database at all, they tuned their queries, which we couldn't touch. Have no doubt, some vendors can't program to find the hole in their backside with a data map, specific queries, and the light of open source code.
Paraphrasing Hanley "A 23GB SQL database and adding approximately 83,000 records daily." With probably mass selects for the IIS.
My company had less transactions and sub 10GB DB's which forced us off a dual 550 1G Ram onto a onto a hyperthreaded dual 2.2Xeon with 2GB ram and a connection to a SAN. Giant leaps.|||Guys
Sorry its taken so long to reply, I've been on a course the last 2 days and haven't been in the office all week.
I will check the spec of the servere tomorrow but off the top of my head it is as follows:
4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)
Will have to check the details of the RAID controller
Can I just say that CPU resource on the server is never really above 20-30% so I didn't think this is the bottleneck
Will check the specs of hard disk etc tomorrow
Cheers
Hanley|||Originally posted by Hanley
I will check the spec of the servere tomorrow but off the top of my head it is as follows:
4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)
Will have to check the details of the RAID controller
That isn't too bad - details on the controller would be good. What's your backbone like. Hubs? Switched? Fiber, 10, 10/100?
Just pointing out other things to look at.
We have a 23GB SQL database and we are experiencing some pretty dire performance problems.
I think this may be related to the disk setup.
We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)
We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.
On a daily basis we are adding approximately 83,000 records.
Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.
Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.
In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.
We maintain our redundancy as both disks are mirrored and we also have offsite backups.
Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)
Many thanks for you help guys
HanleyOriginally posted by Hanley
Hi guys
We have a 23GB SQL database and we are experiencing some pretty dire performance problems.
I think this may be related to the disk setup.
We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)
We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.
On a daily basis we are adding approximately 83,000 records.
Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.
Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.
In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.
We maintain our redundancy as both disks are mirrored and we also have offsite backups.
Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)
Many thanks for you help guys
Hanley
Its been a while since I've had to beat on this one. So bear with me while I throw some questions at you.
What is the general setup of the server? CPU(s)? RAM?
How many SCSI controllers do you have?
How much memory do they have?
How fast are the SCSI drives?
Can you do replication of the database to another server for read only IIS?
RAID 5 is typically faster than RAID 1 in the real world. And if your choke point is the controller, throwing all the drives in the world at it isn't going to help.
In addition even splitting the disks for transaction logs and the datafiles is not going to give you much more speed if they are going against the same controller. As a matter of fact it may even slow you down.
If your C/D are on a separate controller from the E, then run your db files to c/d and your logs to e.
Just how I see it....|||on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.|||It also sounds like your OS is on a RAID 5. If the OS is a raid 5, then the swap file is probably there as well, so the system has to calcualte parity for the swap file space every time it writes to the virtual memory. This can hammer you performace really, really badly.
I would recomment installing a separate partition or disk, separate from the RAID arrays, at least any with parity, and point the vitual memory swap file to live there.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.
Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.
It depends on the SCSI controller and drives being optimized. If so the the three physical I/O's should be running concurrently (write/3 plus parity) vs. RAID 1 being paralell(sp?).
I grant speed diff of RAID 5 on 3 disk is questionable depending on HW, Firmware (FW), and SW vs RAID 1. Running RAID 5 on 5+ disks will generally show the difference.
Another point in RAID 5 favor though depending on OS/FW though is continued expandability. I think the high end SCSI size is about 320GB and you are talking big $ per. In RAID 1 you are limited to that, unless you do RAID 10. but then you might as well look at SAN/NAS. With a good RAID 5 you can add drives to the limit of the controllers.
But if he is trying to run those databases and transactions on a 2x500Mz with 2G RAM a FastSCSI 1 controller and a 10/100NIC, he might as well be trying to pump out of the Pacific using a hand bailing pump and a straw.|||Originally posted by craigmc
Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.
RAID 0 = Striping
RAID 1 = Mirroring
RAID 5 = Striping w/ partity
RAID 10 = Striping w or w/o parity and mirroring (generally with parity)|||craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.
also, it becomes a defacto standard for small to medium size business that do not utilize san technology, to put the data on raid-5 with 5+ drives spread through 2+ scsi controllers, logs on raid-1, also multiple controllers to allow for semi-parallel io. I also recommend to put tempdb on a separate controller, maybe even 1 or 2 non-raided physical disks. AND i appload your suggestion on the location of the swap file.|||and watch paging (total pages), cpu (total pct) and logical disk (avg disk queue length).
This will give you a good first approximation at whether you problem is not enough memory (too much paging), not enough processor (cpu utilization) or insufficient disk performance.
Microsoft recommends that paging be kept below 20 pages/sec.
I suggest if the CPU(s) are spending a lot of time above 80%, you might need faster or more processors.
But I suspect you're problem is disk performance. The thing that REALLY leaps out at me is you have both the transaction logs and data on the same drive. That's nearly always a bad idea, unless you are just too constrained by money to have it any other way. Probably nothing will speed up your response time more - particularly if you're doing a lot of data changes as opposed to mostly SELECTS - than separating the log from the data files.
No doubt, RAID 1 is faster than RAID 5. RAID 10, if you can afford it, is even better. Sometimes it's hard to convince people of this, but RAID 5 is SLOW to write data. So if you're doing a lot of INSERTS, UPDATES or DELETES, get off of a RAID 5 array, it's probably killing you.
ALL THAT SAID, keep in mind that throwing hardware at a performance problem is the last avenue you should persue. If you have performance issues, first TUNE YOUR SQL. One frequent but inefficient query can cost a lot of machine performance. Next, TUNE YOUR DATABASE. Do you have the proper indexes to make your queries execute quickly? Finally, TUNE THE ENGINE. SQL 2000 does a good job of this by itself, but there may be parms you can change to improve it even further. These three steps, particularly the first two, at least have the potential to give you far greater performance gains than any amount of hardware. Only after you've convinced yourself that the common queries are sensibly formuated and have the proper indexes should you even consider spending money on faster hardware. Better hardware probably won't gain you more than 2x to 10x your current speed, but tuning can literally get you gains of 100x - 10000x.
If necessary, use the Profiler to analyzer incoming queries. Take the frequent ones, paste them into Query Analyzer and take a look at the Query plans and I/O costs. Build (or add columns to) indexes to eliminate as many of the table and index scans as you can and get the engine doing index seeks. Time and time again I've seen a little work here yield eye-poping gains.|||Originally posted by ms_sql_dba
craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.
Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.|||Originally posted by craigmc
Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.
No problem. You are right about the swap file.
Until we know a little more about the server he's running on we're just shooting in the dark as to disk access being the problem.
And tuning can help, but some of us are really maintaining databases that are for applications delivered by vendors. We had one application delivered by a vendor that the EOD process had climbed north of 6 hours over time as the data increased. After complaining enough they finally delivered the next version which dropped it to 30 minutes with the same amount of data. They didn't change the database at all, they tuned their queries, which we couldn't touch. Have no doubt, some vendors can't program to find the hole in their backside with a data map, specific queries, and the light of open source code.
Paraphrasing Hanley "A 23GB SQL database and adding approximately 83,000 records daily." With probably mass selects for the IIS.
My company had less transactions and sub 10GB DB's which forced us off a dual 550 1G Ram onto a onto a hyperthreaded dual 2.2Xeon with 2GB ram and a connection to a SAN. Giant leaps.|||Guys
Sorry its taken so long to reply, I've been on a course the last 2 days and haven't been in the office all week.
I will check the spec of the servere tomorrow but off the top of my head it is as follows:
4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)
Will have to check the details of the RAID controller
Can I just say that CPU resource on the server is never really above 20-30% so I didn't think this is the bottleneck
Will check the specs of hard disk etc tomorrow
Cheers
Hanley|||Originally posted by Hanley
I will check the spec of the servere tomorrow but off the top of my head it is as follows:
4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)
Will have to check the details of the RAID controller
That isn't too bad - details on the controller would be good. What's your backbone like. Hubs? Switched? Fiber, 10, 10/100?
Just pointing out other things to look at.
Subscribe to:
Posts (Atom)