February 3, 2003 at 2:39 pm
we have a SQL 2000 Server installed on a win2k pc(dual p3 1.5GHz +1GB RAM ,RAID 5).we have s/w developed in VB6/Crystal Reports 8.And at peak time users(nodes) connected to the Database (server) is 30(may raise in future).
Main Tables having .7 and 2.5 milllion records respectively
a)Memory COnsumption of SQL SERVER Always around 900MB
b)facing problem of speed.some times timed outs/deadlocks.
c)many Qries are in SPs and all select qries having <With NOLOCK> option for the main tables
Can u help me to speed up the entire SQL Server
Is my SERVER configuration sufficient for expected load.
awaiting ur prompt reply
MayurArt
MayurArt
February 3, 2003 at 2:54 pm
Well when you say 'speed things up' this is a very open-ended request. My initial reaction is speed up from what? Do you have a perception that your workload should be handled in milliseconds no matter how many people are accessing your application? Before you start making any changes at all, you need to capture a set of statistics that show your current response times, because without that you aren't going to be able to measure the effects of your changes.
With that out of the way, you can make a start. The data you've given us is not enough to base a prediction on or make generalisations. You mention the use of RAID5 - fine for a database, but not for the transaction log. Can you put the transaction log on just a straightforward disc system? Do you need transaction logs at all, or would using the simple recovery model suit your needs with full backups every 3 or 6 hours?
Nearly every site has a set of core procedures that are used more than the rest - these are your targets for optimisation. However, it's worth using Profiler to measure long running queries, as you may get a nasty surprise as to what your users are doing. With my previous client, they started re-running overnight reports in the morning because one day there was a tiny discrepancy found that was fixed instantly; but because they were accountants they lost faith in the overnight reports and preferred to run another set of figures every morning without thinking of the impact on the trades coming in from the website. Overnight procedures are usually there for a reason: it could be so prices or other data are not changing because exchanges are closed; but it could be because your key report is a cpu hog and brings the system to it's knees. So check what people are really doing.
Often a minor change to index keys can make a huge difference. Remember: the clustered index is in every non clustered index. So if you have a clustered index on say client code, you don't need client code plus currency code in your non-clustered index - it's just a waste of space.
Regards
Simon, UK
February 3, 2003 at 3:02 pm
We have had problems in th epast performance-wise with Crystal Reports. Have you noticed any patterns regarding # users running crystal reports and timeout issues? Can you identify whether certain procs that are timing out running through crytal?
February 3, 2003 at 3:40 pm
With regard to point a) (memory always around 900MB), that's what you would expect with 1 Gig of ram. SQL will use all you give it, and generally use it quite well.
All of our apps are written in VB6. Deadlocks are almost always (in my reading and in my experience) due to lack of indexes and bad (by this I mean inefficient) code. You always want to access tables in the same order if possible from your client, read the data you need immediately, and close the recordset as soon as you can. Make the recordset read only when possible rather than screwing around with lock hints.
I've made subtle changes in queries (usually using QA and turning execution plans on) and had drastic improvements.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 3, 2003 at 10:31 pm
thankzzzz everybody
i need to try to change recovery model.
and indexes... how can i put indexes right idexes or revive them(using profiler+Index Tuning wizard?)
MayurArt
MayurArt
February 4, 2003 at 2:44 am
Well I would look at the execution plan of your top queries and slowest performing queries and take it from there. I am old-fashioned, and have an unhealthy suspicion of anything with 'wizard' in it's name...
History suggests that a wizard will make a rough guess at best and a complete mess at worst. Therefore I wouldn't touch the indexing wizard at all.
Regards
Simon
February 4, 2003 at 3:11 am
Well..
First start the Profiler and profile the Long running Queries....
Or more time consuming but you will see more.
Start the profiler with the 'SQLProfilerStandard' profile. This one also records duration....
Then have a look at everything that is suspicious... ( Has a duration of more then 16 msec )
Then look at what or who is issueing this query. And have a look At the query in the query analyzer.
Also look at repetitive queries. A lot of times the same query implies they are walking a recordset sequentially, maybe could be fixed with one join.....
Also look for cursor use ( SP_Cyursorfetch )
this is also a indication of sequentially handeling stuff.
Furthermore..... I have had bad experiences wit Crystal. I Don't know if it what was the reasoon but Crystal always read all the data ( don't know why )
February 4, 2003 at 4:28 am
My guess is that you do not have a resources problem, but a locking problem. Your users are waiting on each other.
Are you sure about the nolock option?
Are tables always updated in the same order?
Do you have transactions that wait for user input?
Maybe crystal reports scans the whole table.
You might try to disallow row locks and page locks with sp_indexoption on the tables used by crystal reports
The deadlocks will be gone.
The query's and updates will be faster, because a lock on the whole table is faster than hundreds of row locks.
No two people can update the same table at the same time, but with 30 users that is not very likely as long as the query's are fast enough.
Joachim.
February 4, 2003 at 4:31 am
quote:
we have a SQL 2000 Server installed on a win2k pc(dual p3 1.5GHz +1GB RAM ,RAID 5).we have s/w developed in VB6/Crystal Reports 8.And at peak time users(nodes) connected to the Database (server) is 30(may raise in future).Main Tables having .7 and 2.5 milllion records respectively
a)Memory COnsumption of SQL SERVER Always around 900MB
b)facing problem of speed.some times timed outs/deadlocks.
c)many Qries are in SPs and all select qries having <With NOLOCK> option for the main tables
Can u help me to speed up the entire SQL Server
Is my SERVER configuration sufficient for expected load.
awaiting ur prompt reply
MayurArt
A is not that big of an issue unless you don't have more than 900 MBs physical memory.
B and C could be related to Crystal. I have myself as have other developers here fallin pray to the fact that Crystall does not build intuitive queries and we pull them out of the Query of Crystal and put in an SP. You need to evaluate the queries that are submitted and make sure they have proper indexes, stats on most used columns (especially inregards to indexes), any calculation or gorupings that are rolled up in Crystal try to reproduce with Query alone and submit to Crystal for beautification only (in otherwords use SQLs more powerfull engine to create the results as close to output versions as possible and reduce the amount of traffic going to Crystal).
Could be lots of other stuff such as index rebuild or defrag that can have an affect.
February 4, 2003 at 7:43 am
but for crystal reports i donot use any sort of join.i have created one table for that.everytime i fire a report. i dump data into it and bound that table to Crystal report.
main 2 tables having approx 1,3 millions records are havily used almost in 70% qries. first one having Clustered index(Also PKey on 5 cols )and 2 other indexes on imp columns(i suppose)second table also Clustered index(PKey 6 cols) and 2 non-clustered indexes.
In Execcution plan of few Qries it
gives 2-5 as estimated Cost.
more RAM is needed as having 1GB currently????
MayurArt
February 4, 2003 at 9:38 am
I'm with Antares686 on this one. My experiences have been in finding that someone created the queries for Crystal using that secretaries tool for query building that comes in Crystal, performed calculations, aggregates, etc... using the crystal engine, performed the sorts in crystal, and even criterialized their datasets in crystal. This is perhaps one of the most serious performance killers I've encountered where reports are concerned. Crystal reports was never meant to be a database engine. It's a pretty pretty tool.
I use a field definition file for the field structures, to prevent the neccessity of a connection to the database to define the report. A stored procedure to return the data in the defined format, and let the database engine perform the database functions. This methodology seperates the Crystal Report from the database, and returns performance related issues to a DBA, rather than a report writer. Now, the DBA can optimize the stored proc, address concurrency issues, and profile the server for index and table usage to address performance issues. And the report writer can pretty up the dataset for management....
February 5, 2003 at 2:06 am
Mayuresh...
I am wondering why you mention the 1 GB... 1GB is NOTHING, even my home computer has more..... So 1 GB is really NOTHING.
Furthermore I am really curious at what all the suggestions have brought you.
Do you have a clou where to start......
Furthermore i don't think it is a wise thing to create a table and insert the records in them.... BEcause now the data has to be read, written and read again.... But that is a choice you made.....
Furthermore I guess it is a kind of temp table and I hope you are using TRUNCATE TABLE to empty the table and not a delete statement... If you are not using the truncate here is the first win.... Truncate is nonblogged so executes MUCH faster than a delete. Also the harddisks are spared....
February 5, 2003 at 2:23 am
I also have 1.5Gb of RAM on my desktop, but then it doubles as a development system as well. Whilst 1Gb of RAM is not much these days, there is no need to make such a big thing of it. SQL Server runs very happily with 1Gb of RAM and so it should. I am puzzled by a comment you have made well0549, perhaps you could explain:
"Furthermore i don''t think it is a wise thing to create a table and insert the records in them.... BEcause now the data has to be read, written and read again.... But that is a choice you made....."
Well that's what databases are for, so if you think otherwise, perhaps you shouldn't be in this forum?
Regards
Simon, UK
February 5, 2003 at 4:26 am
Simon
Muyaresh states the following :
but for crystal reports i donot use any sort of join.i have created one table for that.everytime i fire a report. i dump data into it and bound that table to Crystal report.
I Agree that is what databases are for.. But that doesn't mean this is a good practice.
Lots of IO, Lots of Log writes etc.......
He is complaining about performance... I would call this a performance killer.....
A Sp returning the recordset or a straightforward query would be better than reading inserting and reading again.....
I hope you understand what I mean......
Then i would like to refer to your last sentence.... I shouldn't be on this forum.
Well let me comment on that. I personally think that 1 GB of memory is the minimum for a PRODUCTION SQL Server.
But while we are on the subject, Muyaresh should look for paging, if there is paging, the 900 MB is not enough. If there is no paging, it is enough.......
Furthermore, SJCSystems i was trying to help.......
February 5, 2003 at 5:03 am
Sackcloth and ashes etc. Sorry to upset the situation, I misread your suggestion on the table inserts stuff as being something frivolous.
Anyway, on the subject of memory, I agree completely, however I still think back to the old days and wonder how much tighter some of these server systems would be if memory were in short supply. By tighter I mean better written, squeezing every last bit of performance from them.
Thanks for your clarification,
Simon
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply