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 |
|
telmessos
Starting Member
4 Posts |
 Posted - 2011-09-12 : 05:36:19
|
| Hi all,I have an application and a SQL server. There's a GIS (Geographical Information Systems application which you can zoom in and zoom out on a map and see the objects on it, similar to Google Maps) application running on the application server and SQL server 2005 Standard edition runs on the database server.Database server specifications are. 64 bit Windows 2008 Server Operating System4 x Intel Xeon E7530 CPUs64 GB RAMThe size of the database is 3 GB, but some of the tables have 1 to 2.5 million rows.My problem is when I make a zoom on the GIS Application around 7 queries (with views) goes to the database server and the database server responds very slow. I tried to search on the internet about adding indexes to the views but unfortunately can't add index as there are image fields on the views.SQL server's automatic caching not performing very well as the queries sent by the application is always changing (it depends on the zoom level on the map, starting point of the zoom etc.) Also I checked the server and saw that sqlservr.exe is only using 2.4 GB of ram. My questions are:1) Is it possible to increase the usage of RAM up to 50 GB etc?2) Is it possible to add the entire database to RAM?Each zoom takes around 9-16 seconds to complete which is unacceptable in my conditions. Please helpMany thankstelmessos |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-12 : 06:35:22
|
| 32-bit or 64-bit SQL?Have you confirmed that the slow-down is due to fetching the data off disk? In my experience, that's not too common, especially with a very small database.--Gail ShawSQL Server MVP |
 |
|
|
telmessos
Starting Member
4 Posts |
Posted - 2011-09-12 : 07:31:05
|
| Thanks Gila Monster,SQL server is 64 bit. The tables contain Geographical Vector Data and there's indexing for the related fields. The layers of the object on the map are coming from these tables using Views. Each time you zoom in or zoom out. Around 7-8 queries going to SQL server. And because of the number of rows on the tables and views each query takes around 0.7 second to 1.3 seconds. Considering the 7 queries in each zoom in/out average time of 7 secs is the waiting time. Once the application server gets the related information from DB server, pictures load in a blink of an eye. So I need to decrease this 1 sec average time for each query. Server has 64 GB of RAM and mostly 55 GB is free. I want the SQL server cover all data in the database in the RAM.Also I've never seen the SQL server capturing more than 2.5 GBs of memory and I don't know how to increase it to cache all information on the database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-12 : 07:56:06
|
| Database of 3GB with 64GB of RAM should be very fast.Do you have non-SQL applications installed on the same server? If so have you limited SQL Server's RAM usage to provide "space" for the other applications? otherwise things will be paged out which will slow everything down ...Is your database tuned to have appropriate indexes where necessary to provide best-performance for your Queries?"I tried to search on the internet about adding indexes to the views but unfortunately can't add index as there are image fields on the views."You need to change the structure of the VIEWs to avoid that then, although having the appropriate indexes on the underlying tables should be sufficient in most cases (i.e. IME you may well not need indexes on the Views themselves).Are you storing the images in the database? If so have you considered storing them on the disk subsystem instead (and storing the disk location in the database records). This would allow you to move the images to a different root location (different drive, or server even) when image retrieval limits performance (although that may not be the case for you, or not for you YET!)Do you do routine housekeeping -= in particular Index Rebuild/Reorganise and Update statistics?Are your NDX files pre-assigned space to prevent they growing ad-hoc (which may cause multiple small extents to be created, which may hurt performance). (Similarly if you have been using SHRINK it is likely that your files have become fragmented)"SQL server's automatic caching not performing very well as the queries sent by the application is always changing"That should not matter if you are using parameterised queries. If your queries are hugely variable then using dynamic SQL, but will with parameterised queries, will mean that the query-plans of the more commonly used queries are cached."So I need to decrease this 1 sec average time for each query. "When you say "Around 7-8 queries going to SQL server" and they individual queries from the application? If they were wrapped into a single query, or a Stored Procedure, there would only be one round-trip to SQL which may help."I checked the server and saw that sqlservr.exe is only using 2.4 GB of ram."SQL will increase its RAM usage dynamically up to the maximum it has been configured for, as it needs it. If your SQL Service was recently restarted then the memory-used is likely to still be low (and will increase over some hours / days)"I've never seen the SQL server capturing more than 2.5 GBs of memory and I don't know how to increase it to cache all information on the database"Given your database is 3GB maybe what you are seeing is all that SQL Server has needed to date? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-12 : 08:19:54
|
| SQL uses as much memory as it needs. You can't directly control that.You never answered my question, have you tested and confirmed that the poor performance is purely from having to read the data from disk? If not, then you're heading down the complete wrong path in trying to force SQL's memory usage.If you're not familiar with query optimisation, may I suggest you consider getting someone in to look at the database performance?--Gail ShawSQL Server MVP |
|
|
|
telmessos
Starting Member
4 Posts |
Posted - 2011-09-12 : 08:58:50
|
| @Kristen : ** Database server is dedicated to MSSQL. No other applications running on it. ** I can not make any changes on the query structure of the system as it is an application package programmed by other people. It runs without any performance problems on other servers. But those companies don't have that many rows on their databases. So I can not change the view structure but the tables which are used on the views have necessary indexes.** The queries are parameterized** I am not sure if it is possible to wrap the queries into one SQL query as each of them draws a different layer on the map. Separate properties, separate tables etc.@GilaMonster : I have same configuration of servers for other data owners. Same application server settings, same database settings. The only difference is number of rows on the tables. I made the hardware tests to the servers which the results are fine. I tried everything I found on the internet. But improvement was only 3-4 secs. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-12 : 09:07:38
|
| Sounds like you need to get the Application provider to diagnose why they queries are slow. I think there is a strong possibility (if your site has more data, and it works OK on systems with less data) that the application is not optimised properly / as much as it can/should be.I have worked on systems that are "poorly written" (in the sense that they didn't have appropriate indexes, and were not using parameterised queries) and it was not uncommon to get a 100-fold improvement in performance. Its cheap and easy to write queries that work on SQL. It takes a lot more time, and usually some skill too, to write good, performant, queries.SQL has performance monitoring tools that will enable you to see which indexes are being used for which queries, whether the query uses the index in the most efficient manner (or whether it "thumbs through every single entry"), and so on. That would, for me, clarify for definite whether the application is written reasonably optimally, or very-non-optimally, and thus give you ammunition to push-back to application developers if necessary (and if not then you may be able to address the issue with more-hardware) |
|
|
|
|
|
|
|
|
RAM Usage and Table Caching