SQL Server Performance Forum – Threads Archive

Do my database need optimization?

My company has a website(developed using ASP) which is connected to our SQL 2000 server. From the start, the website is kind of slow catering to around 30-40 users. The ‘slowness’ is still exceptable to our users. It reach the boiling point when the company expanded n now we have around 70 to 80 users. We do not have much data. Its only around 70-80k of rows but the system is so slow that everybody is giving up on it. Is there anyway that I could optimise the database? Does it has any impact on the database performance since our users has double the count? We r using P3 Server(CPU : 1Ghz*2, 2G RAM, 18.2G * 3 Hardisk, RAID 5). I think there is something wrong with the ASP code but I would like to be 100% sure that our database is at it’s best before I could concentrate on the ASP code.

Performance tuning can be tricky. It’s especially tough in Internet-related projects with lots of components running around, like HTML client, HTTP network, Web server, middle-tier components, database components, resource-management components, TCP/IP networks, and database servers. Performance tuning depends on a lot of parameters and sometimes, by changing a single parameter, performance can increase drastically. ASP application performance involves two parts:
-HTML page performance
-Response time Try following on ASP side:
-Avoid lot of images
-Frames are another elements that will slow down the load process
-If you can avoid tables &* nested tables
-Style Sheets to position your HTML elements.
-Avoid redundant tags
-Never use components such as Microsoft Word or Excel to manipulate the data
-When you have more than 100 lines of code in ASP, it is advisable to move that code to a COM component.
-Avoid multiple Request.Write statements and group them in to few. On the network part of it use 10/100 network cards for better performance.
If your Web server and the database server are running in the same server, then it is advisable to move them into different servers. Even you can introduce a new middle-tier server to handle the COM components with MTS. This decision totally depends on the load on the Web and MTS servers.
For information on troubleshooting slow running queries on SQL take help from this KBAhttp://support.microsoft.com/default.aspx?kbid=243589 and follow the suggested steps. http://www.sql-server-performance.com/visual_basic_performance.asp &http://www.xbuilder.net/x.asp?id=175&s=89 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If you want to find the culprit Copy all the SQL statements in your ASP page, paste them in Query Analyser & run. If it takes considerable time as in ASP page loads, Database is the culprit.
If query processing is very fast, you need to look into ASP pages, Security considerations in IIS & database server, ASP PAge designs (as suggested by Satya) etc. Its never good to write SQL statements in ASP page, rather pur SPs in place. That saves lots of network traffic as well as fast processing in SQL server. HTH
Harsh

Follow the tips above. You will most likely find out that the database is not the issue here. I troubleshoot a lot of ASP applications for 3rd party developers and in 90% of the case it’s not an issue with the database or database server unless it’s due to a really bad database design or you work with large amount of data and you have no or bad indexes. In most cases performance issues occur do to bad coding in ASP (often related to your data access technices) Some common issues I encounter:
– Overuse of server side cursors instead of using client side cursors and disconnected record sets. Combined with pessismistic locking on the cursors this can cause major performance issues with many users in an ASP application. – Not doing set based queries. For example creating a loop in ASP that goes through an array of values and for each value a SQL query is issued. – Other loops of some kind in the ASP code. Loops that works greate when being tested on a developer machine with a single user but in a multi user simply does not work as planned. Often CPU related issues here. – Improper use of ASP session variables. For example putting objects in there instead of just data. This can bring your web server to it knees when the number of users increase. – External dependencies. For example grabbing data (XML or something) on another server over a WAN. Issues here depedning on how the call is made. Is it cached, is the web server (acting client here) issuing these queries for every single user etc.

Due to the new structure change, my company has decided to get a software company to rebuilt the system. Now we are using ASP.net and is under testing phase. We noticed that once an user log into our webpage, the sqlservr.exe in our SQL server took up a lot of memory n after we exit from the webpage, it didn’t reduce. Same as the aspnet_wp.exe shot up from 2k to 25k once a single user log into our webpage. We suspect the web application problem. How should I trouble shoot it? and how do i reduce the sqlservr.exe memory usage after the users exit from the webpage? The users will be automatically log in based on their Lan ID and there is no logout future in our webpage. Shoud we put a timeout for it?

SQL server will always take memory as SQL users request it, up to the configured values it has specified, and keep that memory. SQL is a memory intensive apoplication, and this is one of the reasons that it is usually recommended that sQL server lives on a dedicated server. If memory usage on the server is a problem (i.e. SQL is forcing other applications to page, and vice versa), then *consider* changing the memory settings for SQL server. NOTE – this is more of an ART than a SCIENCE – you will need to find the setting that works best for your specific environmnent. I would guess that at the moment, SQL is configured to grab all the memory it needs, and it probably gets to about 1.8 GB on your server, eventually. Correct? review the momeory settings in EM: EM–>your server–>RIGHT CLICK, properties. Take note of the OS memory – it should be 2gbas you mentioned above. Then gotot the MEMORY tab. I’m expect you will have the "dynamically configure SQL server memory" radio button on (default), and that the values on the sliders will be min (0), max (2gb). IF you need to review this setting, you can do it here (or from query analyser, using the SP_configure options – read Books online for details). Hopet hat helps explain the SQL memory bit, a bit <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> cIaO Panic, Chaos, Disorder … my work here is done –unknown

]]>