| Hello. |
At the new place of work, we set the task to deal with the performance of the SQL server. They said that users complain, "slows down, does not work well." I myself do not work in the database, I do not create queries, etc., all from words.
Initial data at the time of writing:
Win server 2008R2 x64 - 125 Gb RAM - Xeon E5 4640 x2
MS SQL 2012 (the number of databases is 30, the total size is 900 GB)
Information on the database is basic, without second metrics and load data per unit time:
Data Base Pages - 11 134 865
Buffer Cache Hit Ratio - 100%
Target Pages - 261 914 624
Cache objects - 96 346
Cache pages - 885 283
To monitor the situation, the main indicators are integrated into the monitoring server and the conclusions are as follows:
The disk subsystem and the processor pool are not heavily loaded. That is, those resources that are quite enough. But with the memory issue. 100 Gb is allocated for SQL, it “ate” them completely. Moreover, it was allocated 80, added +20 for the week, he “finished them off”.
In this regard, the question is, what is the optimal amount of RAM (approximately) I need in this situation (I am aware that this is very rough, without analyzing the number of requests and other parameters for users of the database) and what to look for in the first place for some optimization of the database , without global change.
I will be glad to answer clarifying questions. Thank you very much in advance.
Yes, another "Buffer Cache Hit Ratio - 100%" shows that all reads are made from memory, an additional confirmation.
So let the one who will be engaged in this task look. It’s just that in one minute he will find out at least the state of the system, for which here, on the forum, a whole consultation with questions and answers will be needed.
let's say, the database does not work, and the server settings, apparently yes. I look at the situation and also see that it works, purely by statistics, like norms. But people complain that they are stupid, takes a long time to think, polls, etc. Therefore, I ask what to draw attention to in turn 1. Thank.
| dezhnevo |
And who told you that the server is stupid? Or, in your opinion, on any stupid sql query the result should be issued instantly? Again, blocking ...
| dezhnevo |
Are you really looking for the button “to press when people complain that they are stupid, long time to think, question”?
The algorithm is simple:
1. Find out what requests are stupid.
2. Find out why stupid.
3. Eliminate the cause so as not to tupili.
Do you have a problem with which item?
There are no magic settings that would fly; server settings are one of the tools that, maybe, in one of thousands of cases, it will help.
From the "health parameters" you can still look at the disk queue, it should be small.
In general, according to the algorithm, Gavrilenko Sergey Alekseevich.
| SERG1257, |
Thanks, did avas use this set of scripts? The result is arranged for you, that is, the information that they derive pushed you to move in the right direction? to optimize the base.
| Gavrilenko Sergey Alekseevich, |
The idea of creating such a button, I think, would have pleased everyone :) + - so, but not quite at such an extreme.
I ask a question in the context of database maintenance, that is, if everything is ok with the server’s hardware, then you really need to communicate with users and understand what is bothering you. I have not had time to do this yet, I started working with the server directly. I think this is a logical step. Next I will look at the creation of some tasks to optimize the work of the database inside SQL, that is, creating tasks in the scheduler for, for example, re-indexing, rebuild, etc.
Let's say this: I didn’t work closely and deeply with SQL, this work seems to be starting for me, so I ask more experienced colleagues, we all started with something :) Thank you.
| Pa sabzh: |
1. try profiler to determine the popular place of the brakes (long report, opening a document, etc.)
2. Look at the indices. Do they have enough on important tables / fields? Are there any extra indexes?
3. Update statistics on important tables.
4. First of all, pay attention to large tables.
5. Seek opportunities to clean the extra data: old logs, logs, "ownerless" tables, etc.
6. At the same time, check the backup settings (they may not be commonplace) and the size of the database log files.
7. Is it possible to fix the SQL code in the applications? Nr arrange with (nolock) in the reports.
| And another stupid question on the transaction log |
Is it normal that it occupies (total size for all bases) 86 Gb? (for some database less, for some more) In the attachment the ratio of the size of the database to the size of the trans file. I know that this file can greatly affect the performance in general.
Attached file Size - 120Kb
"I have on the disk" With "500GB of files, is it a lot or a little, advise, maybe wash half?"
| Logs: Depends on the situation, data model and replication availability. |
Anyway, from time to time it is desirable to cut it, because A large transaction can inflate it noticeably, but it does not know how to compress itself.
Even for the Full model, I trim the magazine from time to time. Usually shortly before the full backup (backup log every 10 minutes).
Also look at the size of the TemRDB database. Sometimes, too, grows up to stop working. Especially characteristic of 1C. :)
And still there are frozen processes that lead to the accumulation of locks. Peculiar knee crafts.
It is also advisable to provide regulations for restarting MSCCL, if there is such an opportunity (at night, on weekends, etc.).
I also remember the case when MSCCL 2c5 started to slow down wildest due to the accident of the backup domain controller (who was on a dedicated server).
| Gavrilenko Sergey Alekseevich, |
It's great that you have everything in order with a sense of humor and a desire to teach the lives of other people, but I never got it though a couple of sensible advice from you, as apparently from a person who understands the issue and has the opportunity to share with others, hints , apparently, your username on the resource and the number of messages. My question is not a call in your ear, but an appeal to people who without the “hand-face” can convey information, or give a couple of links to the right resources, like this and further inside to MSDN links https://habr.com/company / technoserv / blog / 336070 /
To the question that you did not immediately go there, I can not answer. Well, no offense. Moyda "to teach materiel" (and how, I am poor, have not been sent there yet :)
The topic is closed, thank you.
| alexeyvg, |
even more, feel free to wash it! :) Well, since in this way
On SQL not 1, I will tell at once. The tempDB size is 30 Gb and it lies on the C drive in the default path on the server (C: \ Program Files \ Microsoft SQL Server \ MSSQL11.MSSQLSERVER2012 \ MSSQL \ DATA)
By the way, this is one of the tuning tips for 1C performance.
For normally developed and stably operating systems, this advice is really not needed.
I had logs from megabytes, to more than 1 TB, and it was normal for those databases. And that I must answer the question "Is it normal that it occupies (total size for all bases) 86 Gb?"
Or you find the right people if you are the manager who was assigned this job.
Or, if you are a junior IT specialist, you work so that there are a number of specialists, read books, and ask, ask. And then a year or two you can easily perform such work in simple, typical cases (which I am sure you now have).
And the treatment by following the instructions from the forum is highly unproductive, and, most importantly, it is boring for the respondents.
Yes, what did you think? Show me your daily dozens of answers to such questions? Do not practice, boring?
Here is the communication of MSSQL specialists, experienced or beginners, and for the remote there are sections "Work" and "Jobs".
| SERG1257, |
Well, yes, IT people are such, suspicious and vulnerable nuturs. Highly and very finely organized :)
As for the scripts, I realized that this is not the same cherished button. I looked. I will try on the sly. Thanks again. Everyone !!!
| Dezhnevo, the server will take away memory no less than the data that is accessed. Plus buffer memory for sorting. |
It can slow down either due to insufficient equipment performance, or due to processing large amounts of data or the presence of long-open transactions (the first may depend on the second). There are a large number of tools for diagnosing hardware and software, the main of which are SQL and Windows performance counters and dynamic administrative views. What to choose and what indicators to expect - can be found on the Internet. I advise you not to rely on one or two publications, but to study a few, the authors are not always accurate in the recommendations.
To adjust the server you have to study and sift an impressive amount of information (and spend time, of course). So immediately decide - do you want to do it or not.
| Vladislav Kolosov, |
Need to do, this question is not worth it. Pro information agree. It is necessary to study what I do. Screwed SQL to Zabbix. Thank.
| Mind |
It is clear that there are people, specialists who will figure it all out very quickly. But I need to understand, so we will read books.
| dezhnevo |
maybe the problem is not in the SQL server, but in the application that slows down? Users not in management studio work. And an application can slow down not only because SQL processes requests for a long time.
| Androgen1985, |
Absolutely true and logical statement. And in this direction, too, I look. There are such suspicions
|dezhnevo, as I understand you. A few years ago I was in a situation of 1 to 1. I started with the top of slow queries and Windows performance counters in terms of the performance of the disk subsystem. This is easy to do and well sets the direction "where to dig further."|
| Ondayl, |
|dezhnevo, do you already have information from the counters and tops of requests?|
By sabzh - I would start working with users who complain about when it “slows down”, what exactly “does not work”.
Otherwise, it is to wander in the dark.
| Ondayl, |
Information is collected. Not all metrics are considered yet.
But blame, of course, SQL Server. :-)))
| Mind |
in my case there is no such thing, nothing but sql on the server is spinning
Let me write a few obvious things that are guaranteed to help (besides jokes) to increase the performance of MSSQLSERVER. True, we are talking about such an increase :-).
1. Separate the bases, logs and tempdb on different disks. Note that it is highly desirable that these be physically different volumes. For example, if you have many disks (10 :-), a mirror is for the system, and 8 are dangling on a Reid controller, then the best configuration will be a floor log mirror, a mirror for temp, and 10 for data.
2. If you have a storage system - take care of priorities when working with volumes. For logs, delay (latency) is crucial, for data - total bandwidth.
3. Be sure to remove tempdb from the system disk.
As a budget "SQL performance accelerator," I would advise you to buy a PCI-EX SSD for a large resource, and bring tempdb to it. There really is a moment here that if it fails, SQLSERVER will stop abnormally, but the destruction of the bases will (theoretically) not happen, and you will be able to start up after some gestures ( https://infostart.ru/public/236716/) within a few minutes reboot.
Well, of course, if you do not have a 24x7 system and you are driving through non-banking transactions.
4. If you do not have an enterprise version of SQLSQRVER, then it is more than 64 (2012, 2014) or 128 (2016SP1 +) to give it to him meaningless. It does not know how.
5. If you have much more memory, for example - 256, and tempdb is actively used when working (see at least the system monitor) - think about it can make a RAM disk on the "extra" memory, and place tempdb there.
6. Limit, in view of the foregoing, the memory available to SQLSERVER. It is better to leave the system 10%, but not less than 4GB.
7. Be sure to install Instant File Initialization https://www.brentozar.com/blitz/instant-file-initialization/
8. If you have a system with 8 cores - make 8 tempdb files, if there are more, then another +4 files for every 8 cores, but no more than 16 files. Be sure to set the files to the same initial size and the same increment. Somewhere 128-512Mb.
9. Set the same increment for all database files that you use. And for data files 256-512, for logs 32-64MB (logs are filled with zeros during increment, so a large increment can suddenly suspend some kind of update request).
10. Make sure the AUTO CLOSE option for DB is OFF. For the option AUTO SHRINK - in general, you need to shoot immediately and without trial.
11. Make sure the auto-update statistics option is ON.
12. Despite paragraph 11. create a maintenance task to recalculate the statistics of the working base, and make it autostart during periods of minimum load.
13. In fact, the task of rebuild / reorganize indexes is NOT NEEDED (for any reasonable scenario using SQL) :-)
14. Make sure the Page Verify database option is set to CHECKSUM. If this is not the case, immediately set this option and run DBCC.
15. Make sure that you regularly make a full backup of the database, and backup of the database log, for all databases with a FULL recovery model.
16. Please note that the SIMPLE recovery model has NO advantages over FULL in speed :-)
17. Transfer the base to the SSD.
18. If you can not do this, and you have 2014+ SQL - at least enable BPE https://olontsev.ru/tag/buffer-pool-extension/
But keep in mind that the SSD for this must sit on 6G SATA at least.
19. For the practice of Shrink DB - you need, for good, to shoot. This should be done only if you are clearly aware of what you want to do and why, and what the consequences will be.
20. Remove the system index flag for a quick search on the volumes where the databases are located.
21. Add files with databases to antivirus exceptions.
22. Disable generation of 8.3 names and last access time on volumes with databases.
23. You can quite increase the speed of the database, properly setting the indices. But for this you need to understand what you are doing and why :-)
:-) theoretically controversial questions are marked, which, however, in 90 +% of cases work exactly as I said :-)
| uaggster |
First of all, thank you for not being too lazy to answer. Of the many articles and videos that are being processed now, in most cases I agree with you.
I have only one question on physics, I break tempdb into parts, now there is one and on the system slow screw (this is any bottleneck), everything is clear by the number and size of increments, but! from which to build on determining its original size. While the logic is not clear.
For example, now one file is 30 Gb in size, I divide it by 12, since 8 + 4 in my case, the percentage of growth is set at 10% and without limitation (although the recommendations say that you should limit the growth by time, at least 2 minutes), sudden abnormal growth is not observed. The question is, what is the initial size of each file to indicate, which formula calculates this? For me, it is not clear.
The question on rebuild and re-index, after transfer to ssd, I think is not relevant, and even contraindicated.
Taki no. This is a very bad decision. But on the contrary - a mirror for the database and raid10 for tempdb, this is kosher.
And ready to explain why.
When one of the 4 disks will fly out (and it will definitely fly out) - then the rebuild volume under tempdb is not scary. Disable users, run an extra backup from the database. Plus the speed of writing and reading for tempdb is almost always more important than for a large database.
But if the disk flies out under the database volume and the rebuild starts - you will fearfully expect that when you perform a backup due to the increased load, the second disk in the same mirror will fly out of the straype - and lose a lot of fresh data.
If you transfer tempdb to a separate disk, then I advise you to set the initial file size = disk space / number of files (well, minus the space for a ~ 1-2GB log, tempdb is generally not needed), incrementally 0.
However, if you do not enable instant file initialization, you risk getting a SQLSERVER start within tens of minutes :-)
The percentage increase in files to use EXTREMELY is not recommended.
Set a fixed gain.
The meaning of Rebild and Reindex is not that.
Simplified, the point is this:
With intensive inserts and data updates, "holes" can form in the clustered and non-clustered indexes. Pages, and data on pages that are marked as deleted.
SQL also reads data with disks in chunks of 8kb, and displays them in memory also in the same chunks, as a whole, without modification.
And imagine that he pulled a page from the disk, displayed it in memory, and out of 8 kb, only 2 kb is useful there, the rest is marked as deleted.
First, he pulled 8 kb from the disk, of which only 2 were useful (the load on the disk), and secondly, he remembered 8 kb and so, despite the fact that there are only 2 kb useful.
This is what should be corrected by reorganization or rebuilding.
But it depends on the intensity of the use of tempdb.
If you have, for example, versioning in the Bug, then maybe yes.
And here, for example, if you have a 500 gigabyte database, and there is only 16 memory, and the requests, let's say, are non-local. Most likely, the time of data recovery from the disk will be decisive.
Why? Logs on another disk.
Well, I'll save the tail of the log. Or I will back it up more often.
Here is another nuance. Suppose you have 4 X TB drives, you merged them into RAID-10, got 2 * X TB. One disk has disappeared, there is nothing to replace. Everything is bad.
And now you have the opposite situation. 6 disks on X, a mirror under a DB, RAID-10 under tempdb. A disc flew out of the mirror. We got a spare from the safe or stuck a hot-spare into a mirror - and the rebuild doesn’t go. Disc with bedami. And they thought they were kosher. What to do.
To collapse a RAID-10, to assemble a volume with the same letter on the mirror, under tempdb, there will be 2 times less space - no big deal. One of the received backup disks should be sent to the volume from the database.
Similarly, a disk of RAID-10 crashed under tempdb - they took 2 out of 3 and assembled a mirror under tempdb. Has slipped speed - not important. A kosher spare disk will come - collect the RAID-10 back.
Here, in the next topic, a colleague cries bitter tears that the authorities bought a high-speed regiment for the backups of the combat servers - and gave it to other tasks. And backups are flooded to the attached external drive. Not even on a NAS with a mirror.
In universities and laboratories, you can experiment with the location of data on disks. And in firms, everything is decided by the budget, which is always cut, which is always not enough for the most important things. And the speed of queries to the database is not as important as data integrity and downtime on the combat server .
| Andy_OLAP, |
The base does not fit under X, but it will fit into 2 * X Tbyte - this is not a reason to put it on RAID-10. This is a reason to go to the authorities and knock out the budget for replacing disks - ALL disks - for more capacious and speedy PLUS, think over the question in advance that the size of backups of such a base is also growing, and you will probably need to increase disks on more capacious ones and on the shelf, where backups are lying. No need to cut out 7 caps from the skin of a rabbit in advance.
17. funny. on one thing?
Did you know that the bandwidth of the SSD - nikakuschy. Here the more spindles the better.
18. How far were they from the people?
Someone else bought on this bullshit?
|For the rest of the points, also a finger to the sky. What if it already is? Then what do you advise?|
Why on one? On the mirror.
Well, watch out for their condition :-)
And you try a couple of items to turn on, and enjoy the result.
These are "level zero" tips.
To do NOT so - you need to understand why you did it. If this has already been done, but it still slows down - then the problem is already beyond the competence of the vehicle. Let them hire a consultant or a vehicle let them sit down for courses.
By the way, will you demonstrate your TOP (23) "what you need to do / check on the braking server in the first place" so that we can see how this is "not a finger to the sky"?
| By the way, I forgot another point: |
24. If the bases are not on a separate shelf, but on volumes connected to the server's Reid controller, check if there is a battery on the server controller and whether Write-back caching mode is enabled.
To increase performance, it is highly recommended to disable the disk cache on the controller with a battery and enable the Write-back controller to cache data.
This can be done without a battery. But then press in a chair and make backup more often, especially backup logs. :-)
In the same place, in the controller settings, there is a setting for the read ahead read ahead mode.
I can’t say anything definite about her. If the application pulls one record from the database, then you need the "no read ahead" mode. If, on the contrary, we are talking about a database of statistics, and the application reads the data in large chunks - then "with prefetching reading."
Try this and that, and measure performance.
Regarding the raid of the controller and parameters, I fully agree with you, and add a little more about read ahead, I read somewhere that there is one more provision of this parameter, it seems to be adaptive. The bottom line is that the controller itself determines whether forward reading is necessary or not, depending on the request flow, and can both turn it on and off.
On my "adaptive" no :-(
And about pre-emptive reading - in my practice there was a case when its shutdown (shutdown, aha) gave a performance gain of about 10 percent. And, as a result, a set of reports that were generated 10 hours, and usually did not have time for the planning meeting - It began to be generated less than 9 hours, and to the planning meeting I began to keep pace, than threw a pack of yeast into our well-established ... uh ... latrine.
But there was 1C, and everything is difficult. :-)))
|In my practice there have been cases when moving bases on an SSD has impaired performance. But what is the reason, I did not find out.|
The SSD is not all right with the performance beyond the SLC cache.
The size of this cache is quite large (tens of gigabytes + -), but everything is quite exhaustible by the standards of the database.
In this case, about a slight decrease in performance - they lie, sometimes there is a 10 or even 20 times decrease.
Those. If, for example, we pour dozens of gigabytes of bulk (even gigabytes), then we can easily fly out of the slc cache and get 10 Mb / s (exaggerated) for recording.
In addition, there is such muck as trim. Flash cells must be pre-cleaned before recording. Therefore, if information is intensively overwritten on a disk, a situation may arise when the disk is empty, but there is no place to write, and the disk should start garbage collection.
Normal server SSDs can do this quite well in the background, but even they do it poorly, with a drop in performance several times, even dozens of times.
That is, if we have a situation where we update (for example) even a small number of records with a very high frequency - after some time we will receive periodic lags.
Total - SSD, of course, a killer feature, but ... let's say, under reasonable conditions of use.
I have no more questions.