December 15, 2003 at 1:47 pm
Does anybody have an experience with Patrol for MS SQL? It is the product of BMC Software. My manager want the report on the memory usage per database, I don't know which tool can implement it.
December 15, 2003 at 2:17 pm
We have BMC Patrol but never heard it can report memory usage per database.
December 15, 2003 at 2:23 pm
Do you know which tool has the function?
December 15, 2003 at 2:35 pm
I don't know and I doubt the existence of such tools for SQL Server. I would like to know too.
December 16, 2003 at 6:26 am
Hrum, memory usage per database is a task that may not be obtainable. SQL brings pages in and out of memory as needed and doesn't generally allocate any fixed memory for a single database. I would look at usage and size that will tell you if a database is taking the bulk of the data cache or not. The mystery tool would have to look into the data cache and determine what pages game from what database I don't think that can be done.
Wes
December 16, 2003 at 6:56 am
Knowing the memory usage for a database is not particularly useful.
I have worked on DB2 for z/OS, where information of this type can be obtained. The really useful information is at the table and index level. Aggregating the figures to database level *may* help decide if a database should be moved to a different server, but little else. However, aggregating to server level (which is all we currently get) does help show if server memory is overloaded.
Details that are useful at the table and index level are:
* Number of pages for this object in the buffer cache.
* Number of pages read asynchronously for this object.
* Number of pages read synchronously for this object.
* Lifespan of pages for this object in the BC.
* Level of random and sequential (prefetch) activity for this object
* Hit ratio for this object
* Total number of pages on disk for this object.
With SQL Server, these figures can help you decide if the use of PINTABLE could help performance. They can also highlight tables with poor performance, but SQL Server lacks the facilities needed to rectify this.
In DB2 for z/OS, you have multiple buffer caches, called buffer pools. The size of these is fixed by the DBA, and you can assign a table or index to a given BP. This allows the DBA to protect lookup tables by giving them dedicated memory. The server can also be protected from monster tables/indexes that will always flood memory by assigning them to a small BP that allows table scans to perform well without crowding out other useful data.
Joel Goldstein of http://www.responsivesystems.com is a leading expert on in-depth table and index tuning. If you are interested in this subject, the following presentation is worth reading. It is written about DB2, but over 90% applies to any DBMS:
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 16, 2003 at 9:00 am
It almost sounds like your manager is new to SQL Server, coming from an Oracle or DB2 background. SQL Server allocates memory at the instance level (one instance per server in most cases) where Oracle usually has multiple instances per physical server.
Has your manager told you what he intends to do with the memory usage information? That would be useful in trying to determine what he really wants/needs.
December 16, 2003 at 9:01 am
Thank you guys.
>>I would look at usage and size that will tell you if a database is taking the bulk of the data cache or not.
How to get the information?
December 16, 2003 at 10:13 am
We use Patrol and it basically is an automated way to get PerfMon statistics. If it exists in Perfmon, Patrol can record and smmarize it.
To get the usage pre db, you can compare transactions/sec for each db. You can also look at active transactions for a spot rate. The ratios between the dbs will help determine the load (a little). However transactions are not equal. One transaction could take the same amount of resources as 500 in another database.
A better way, although much more work, is to run Profiler and sum up the CPU or total time (duration) by database and compare the numbers.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 16, 2003 at 12:14 pm
We also use Patrol, and just like Steve mentioned, it is great for getting to the performance counter data and automating actions for exceeded thresholds. I have not seen where you can report on memory usage per database, but you might want to check out the BMC DB-XRay product. It is has been greatly improved, and works more closely with Patrol than it previously did. It can drill down into database details that the base KM for SQL Server cannot.
If looking for tool suggestions is also one of your goals for this post, then I would recommend giving Quest Central for Databases a look. We have that tool also, and currently only use the real-time monitoring and performance based tools, but it does come with an add-on "reactive" monitoring component known as Foglight (which is functinally equivalent to Patrol, but costs much less).
December 17, 2003 at 8:34 am
Just a simple info.
The guys as BMC, Tivoli, HP, MS are not able to show you more then SQLServer itselfs is able to.
An addition there are fairly expensive and also long to implement to get exactly what you want.
If you have only your SQLServer to get the report from I would suggest to write your own PerfMon wrapper (or even use PerfMon itself and save the logs into a file or database).
Those management tools are useful if you have a quite large environment to manage.
Bye
Gabor
Bye
Gabor
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply