I am new to SQL server but not database servers. I worked with Pervasive
SQL for years and i have a couple of questions as our current company is
having some problems.
We seem to be having server speed issues and i cant figure out why. Here is
the setup:
Dual Intel 3 gig Xeons (Shows up as 4 processors because of Hyper theads), 4
Gig RAM
RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
and tempdb
Page File is on the RAID 0
Windows 2003 Server Standard Edition with latest updates
Sql Server 2000 Enterprise with latest updates
We have 60 Users accessing VB front end programs that all access a sql
database on this server.
The current size of the database is 3 gigs
Is there anything i should look at for getting this thing to speed up? It
is really slow on updates and dat transactions.
For some reason no matter what i do when i configure SQL to take more memory
it never does. I cannot get it to take past 1.7 gigs.
Also we are experiancing problem with locks in the system. Some processes
take a very long time to complete (hense the speed question
) and whenthey are running they lock the tables. When they lock the tables of course
the vb interfaces and stored proceedures will not run.
We have found that putting no lock helps out a bit but to go and change all
of the code will be impossible because our vendor will not provide their
source code.
I know in pervasive sql i could take off opertunistic locking which would
stop these issues. I do not see any settings in SQL for this. Row locking
would be ideal because other people can access they same table correct?
Sorry for the book but i want to provide the most information possible.
Thanks!
Rob.could be lots of things, but I'd bet you have index problems.
Lack of indexes,
incorrect indexes
or Fragmented indexes
Run a DBCC ShowContig and see how fragmented you are. That is a good
starting point
Read the SQL Books on Line about "ShowContig" the number I usually I ball
first is "Scan Density". If that number for each table\index is below 90%,
you are experience fragmentation and you should consider runing a defrag
routine (DBCC DBReindex or DBCC IndexDefrag).
You're probably gonna find LOTS of problems, but this is where I'd start if
I were you.
Greg Jackson
PDX, Oregon|||Comments Inline
"Rob" <teste@.telus.com> wrote in message
news:%23bdt%237mdFHA.3492@.TK2MSFTNGP14.phx.gbl...
> Hello All,
> I am new to SQL server but not database servers. I worked with Pervasive
> SQL for years and i have a couple of questions as our current company is
> having some problems.
> We seem to be having server speed issues and i cant figure out why. Here
> is the setup:
> Dual Intel 3 gig Xeons (Shows up as 4 processors because of Hyper theads),
> 4 Gig RAM
> RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
> and tempdb
We begin to see a problem.
> Page File is on the RAID 0
> Windows 2003 Server Standard Edition with latest updates
> Sql Server 2000 Enterprise with latest updates
You won't be able to use most of the SQL EE features on a Windows Standard
Edition OS.
> We have 60 Users accessing VB front end programs that all access a sql
> database on this server.
> The current size of the database is 3 gigs
> Is there anything i should look at for getting this thing to speed up? It
> is really slow on updates and dat transactions.
Not surprised. First you may want to convert the Transaction logs to RAID 1
or 1+0. This will give you more performance and reliability. Same for the
database files.
> For some reason no matter what i do when i configure SQL to take more
> memory it never does. I cannot get it to take past 1.7 gigs.
Upgrade to Enterprise Edition OS. You can then use the /3GB switch or add
even more RAM with PAE and AWE memory. What you are seeing is perfectly
normal under Standard Edition Windows OS.
>
> Also we are experiancing problem with locks in the system. Some processes
> take a very long time to complete (hense the speed question
) and when> they are running they lock the tables. When they lock the tables of
> course the vb interfaces and stored proceedures will not run.
>
This is a bad application design. You may need to restructure some tables
and code to increase performance. The best hardware in the world won't make
up for a bad design. Typically, long processes fall into two categories,
data feeds/batch updates and complex reports. Run the feeds at low activity
times and move the reports to a read-only server. You can use Replication or
Log Shipping to populate the report database server.
> We have found that putting no lock helps out a bit but to go and change
> all of the code will be impossible because our vendor will not provide
> their source code.
Welcome to vendor Hell. Instead of asking them for source code, ask them
for performance metrics on various standard hardware configurations. Hold
their feet to the fire and make them work with you on performance issues.
> I know in pervasive sql i could take off opertunistic locking which would
> stop these issues. I do not see any settings in SQL for this. Row
> locking would be ideal because other people can access they same table
> correct?
>
Opportunistic locking is a client feature. SQL uses row-level locking for
data but you may end up with key range locking that affects many rows if you
have a indexes that are pororly selective.
> Sorry for the book but i want to provide the most information possible.
> Thanks!
> Rob.
>|||Rob,
> RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
> and tempdb
> Page File is on the RAID 0
Better if you use raid 1 or 10.
> Is there anything i should look at for getting this thing to speed up? It
> is really slow on updates and dat transactions.
Raid 5 gives good performance for reading, not for updates.
Performance Tuning SQL Server Hardware
http://www.sql-server-performance.c..._tuning.asp#I/O
> For some reason no matter what i do when i configure SQL to take more memo
ry
> it never does. I cannot get it to take past 1.7 gigs.
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750/
AMB
"Rob" wrote:
> Hello All,
> I am new to SQL server but not database servers. I worked with Pervasive
> SQL for years and i have a couple of questions as our current company is
> having some problems.
> We seem to be having server speed issues and i cant figure out why. Here
is
> the setup:
> Dual Intel 3 gig Xeons (Shows up as 4 processors because of Hyper theads),
4
> Gig RAM
> RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
> and tempdb
> Page File is on the RAID 0
> Windows 2003 Server Standard Edition with latest updates
> Sql Server 2000 Enterprise with latest updates
> We have 60 Users accessing VB front end programs that all access a sql
> database on this server.
> The current size of the database is 3 gigs
> Is there anything i should look at for getting this thing to speed up? It
> is really slow on updates and dat transactions.
> For some reason no matter what i do when i configure SQL to take more memo
ry
> it never does. I cannot get it to take past 1.7 gigs.
>
> Also we are experiancing problem with locks in the system. Some processes
> take a very long time to complete (hense the speed question
) and when> they are running they lock the tables. When they lock the tables of cours
e
> the vb interfaces and stored proceedures will not run.
> We have found that putting no lock helps out a bit but to go and change al
l
> of the code will be impossible because our vendor will not provide their
> source code.
> I know in pervasive sql i could take off opertunistic locking which would
> stop these issues. I do not see any settings in SQL for this. Row lockin
g
> would be ideal because other people can access they same table correct?
> Sorry for the book but i want to provide the most information possible.
> Thanks!
> Rob.
>
>|||There is an issue with SQL Server and HyperThreading. You need to make sure
SQL Server isn't enabled for all of the four processors that show up. I
believe you only want to enable Processors 0 and 2. Search the KB on this
issue.
"Rob" <teste@.telus.com> wrote in message
news:%23bdt%237mdFHA.3492@.TK2MSFTNGP14.phx.gbl...
> Hello All,
> I am new to SQL server but not database servers. I worked with Pervasive
> SQL for years and i have a couple of questions as our current company is
> having some problems.
> We seem to be having server speed issues and i cant figure out why. Here
is
> the setup:
> Dual Intel 3 gig Xeons (Shows up as 4 processors because of Hyper theads),
4
> Gig RAM
> RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
> and tempdb
> Page File is on the RAID 0
> Windows 2003 Server Standard Edition with latest updates
> Sql Server 2000 Enterprise with latest updates
> We have 60 Users accessing VB front end programs that all access a sql
> database on this server.
> The current size of the database is 3 gigs
> Is there anything i should look at for getting this thing to speed up? It
> is really slow on updates and dat transactions.
> For some reason no matter what i do when i configure SQL to take more
memory
> it never does. I cannot get it to take past 1.7 gigs.
>
> Also we are experiancing problem with locks in the system. Some processes
> take a very long time to complete (hense the speed question
) and when> they are running they lock the tables. When they lock the tables of
course
> the vb interfaces and stored proceedures will not run.
> We have found that putting no lock helps out a bit but to go and change
all
> of the code will be impossible because our vendor will not provide their
> source code.
> I know in pervasive sql i could take off opertunistic locking which would
> stop these issues. I do not see any settings in SQL for this. Row
locking
> would be ideal because other people can access they same table correct?
> Sorry for the book but i want to provide the most information possible.
> Thanks!
> Rob.
>|||>> For some reason no matter what i do when i configure SQL to take more
> Upgrade to Enterprise Edition OS. You can then use the /3GB switch or add
> even more RAM with PAE and AWE memory. What you are seeing is perfectly
> normal under Standard Edition Windows OS.
If they are using MS SQL 2000 SP4, then Windows EE will not help cause
SP4 has some problems with AWE.
Hilarion|||The original SP4 problem with AWE was it only allowed half of the available
memory to be assigned to SQL. That issue has been corrected.
http://support.microsoft.com/?kbid=899761
Geoff N. Hiten
Microsoft SQL Server MVP
"Hilarion" <hilarion@.SPAM.op.SMIECI.pl> wrote in message
news:d99rt7$mn9$1@.news.onet.pl...
> If they are using MS SQL 2000 SP4, then Windows EE will not help cause
> SP4 has some problems with AWE.
>
> Hilarion|||I would disagree with this advice. With SP3 or SP4, SQL gets along with
HyperThreading quite nicely. I typically limit the maxmum degree of
parallelism to the actual physical processor count, but otherwise I see
typically a 20%-25% performance boost on a transactional system. Windows
Server 2003 as an OS really helps compared to Windows 2000 on HyperThreaded
boxes.
Geoff N. Hiten
Microsoft SQL Server MVP
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:uhHAssodFHA.612@.TK2MSFTNGP12.phx.gbl...
> There is an issue with SQL Server and HyperThreading. You need to make
> sure
> SQL Server isn't enabled for all of the four processors that show up. I
> believe you only want to enable Processors 0 and 2. Search the KB on this
> issue.
> "Rob" <teste@.telus.com> wrote in message
> news:%23bdt%237mdFHA.3492@.TK2MSFTNGP14.phx.gbl...
> is
> 4
> memory
> course
> all
> locking
>|||I suspect you need to look into your locking strategy as opposed to
federated Databases and\or merge replication.
Greg Jackson
PDX, Oregon|||That is just it. Almost all of the big applications are coming from the
Vendor program. I am unable to see their code. So i cannot stop their
locking. From talking to them they state that, they cannot change the locks
because that is required for updates to the data.
Thanks
Rob.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:uSN0xZqdFHA.1036@.tk2msftngp13.phx.gbl...
>I suspect you need to look into your locking strategy as opposed to
>federated Databases and\or merge replication.
> Greg Jackson
> PDX, Oregon
>
No comments:
Post a Comment