Go to Main page..

SQL Server Performance QUESTION! , Microsoft SQL Server





SQL Server Performance QUESTION! [new]
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:

Server:

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.
Re: SQL Server Performance QUESTION! [new]
dezhnevo
and what to see first
Start from here
https://www.brentozar.com/blitz/
Re: SQL Server Performance QUESTION! [new]
dezhnevo
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)
Roughly, it can be argued that the memory is completely enough, because additional memory, he ate a week, not a few minutes. This means that readings are made from memory, and an additional increase in memory does not change much.
Yes, another "Buffer Cache Hit Ratio - 100%" shows that all reads are made from memory, an additional confirmation.
dezhnevo
and what to see first of all for a certain optimization of a DB, without global changes.
You will not do this optimization, right? Since you "I myself do not work in the database, I do not create queries, etc., all from words."
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.
Re: SQL Server Performance QUESTION! [new]
alexeyvg,

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.
Re: SQL Server Performance QUESTION! [new]
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 ...
Re: SQL Server Performance QUESTION! [new]
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?
Re: SQL Server Performance QUESTION! [new]
dezhnevo
alexeyvg,

let's say, the database does not work, and the server settings, apparently yes.

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.
dezhnevo
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.
So, with the server all the rules, the work of the DBA remained - to understand why people complain and eliminate (or give information to those who will eliminate).

In general, according to the algorithm, Gavrilenko Sergey Alekseevich.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
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.
Thank.

Attached file Size - 120Kb
Re: SQL Server Performance QUESTION! [new]
dezhnevo
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)
Logs should be as large as you need. How can I say a lot or a little 86 Gb?
"I have on the disk" With "500GB of files, is it a lot or a little, advise, maybe wash half?"
Re: SQL Server Performance QUESTION! [new]
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).
Re: SQL Server Performance QUESTION! [new]
L_argo
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.
Oh, well, the hand is the same! Fuck? Disk space is small? Or are you really kicking at the moment when a large transaction arrives and everyone starts to wait for the auto-expansion of the log? Like that all was longer?

L_argo
It is also advisable to provide regulations for restarting MSCCL, if there is such an opportunity (at night, on weekends, etc.).
Another "fuck."
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
alexeyvg,

even more, feel free to wash it! :) Well, since in this way
Re: SQL Server Performance QUESTION! [new]
L_argo

Also look at the size of the TemRDB database. Sometimes, too, grows up to stop working. Especially characteristic of 1C. :)


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)
Re: SQL Server Performance QUESTION! [new]
Another "fuck."
Sometimes it helps a lot if applications are written crookedly. :)
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.
Re: SQL Server Performance QUESTION! [new]
dezhnevo
It's great that you are fine with a sense of humor and a desire to teach the lives of other people.
And nothing that I did not talk to you? Did he try to save you from leftist councils?

dezhnevo
necessary resources, like this and further inside to MSDN links https://habr.com/company/technoserv/blog/336070/
When you realize that everything that is written there seems to be the same as in the case, but it works when the base is normally delivered initially, and it’s not a fact that it will help you, then we’m backing.
dezhnevo
alexeyvg,

even more, feel free to wash it! :) Well, since in this way
You are exactly “in that manner” asking a question.
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?"
dezhnevo
and appeal to people who, without the "hand-face" can convey information, or give a couple of links to the right resources
You are told that it is impossible to solve this problem with a "couple of links".
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".
Re: SQL Server Performance QUESTION! [new]
dezhnevo
Thanks, did avas use this set of scripts?
I look like a man who is not responsible for the market? Of course enjoyed. Only this sp_blitz script NOT CHINESE. only indicates the most common configuration errors. Sets the search direction.
dezhnevo
even more, feel free to wash it! :) Well, since in this way
http://citforum.ru/howto/smart-questions-ru.shtml#keepcool
Re: SQL Server Performance QUESTION! [new]
SERG1257,
Thank.
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 !!!
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
Gavrilenko Sergey Alekseevich
dezhnevo
necessary resources, like this and further inside to MSDN links https://habr.com/company/technoserv/blog/336070/
When you realize that everything that is written there seems to be the same as in the case, but it works when the base is normally delivered initially, and it’s not a fact that it will help you, then we’m backing.
And I really liked the very first comment with a huge number of questions to the authors of this article, which ends with the words:
I'm exhausted. There are still questions, and the article is about nothing.
Re: SQL Server Performance QUESTION! [new]
SERG1257
dezhnevo
Thanks, did avas use this set of scripts?
I look like a man who is not responsible for the market? Of course enjoyed. Only this sp_blitz script NOT CHINESE. only indicates the most common configuration errors. Sets the search direction.
Brent is good at selling his services. Further it says that if you are fucking about the number of "mistakes" that my procedure gives out, but you are too lazy to read books and understand all this, then hire me, I will fix everything for 3 days and a huge amount of money.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
Androgen1985,

Absolutely true and logical statement. And in this direction, too, I look. There are such suspicions
Re: SQL Server Performance QUESTION! [new]
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."
Re: SQL Server Performance QUESTION! [new]
Ondayl,

:)
Re: SQL Server Performance QUESTION! [new]
dezhnevo, do you already have information from the counters and tops of requests?
Re: SQL Server Performance QUESTION! [new]
Mind
Brent is good at selling his services.
like it's bad
Mind
I will fix everything for 3 days and a huge amount of money.
American miscalculated, will not pay him dezhnevo a penny

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.
Re: SQL Server Performance QUESTION! [new]
Ondayl,

Information is collected. Not all metrics are considered yet.
Re: SQL Server Performance QUESTION! [new]
SERG1257
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.
Brake yuser, do not respond to server requests.
Re: SQL Server Performance QUESTION! [new]
SERG1257
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.
+1 I was once told that the server is slowing down. Began to watch what the user does, and there the application unloads data from the server with one request in 30 seconds to the local cache, then disconnects and starts to shovel something, create excel files, etc. and so on for half an hour.
Re: SQL Server Performance QUESTION! [new]
Mind
SERG1257
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.
+1 I was once told that the server is slowing down. Began to watch what the user does, and there the application unloads data from the server with one request in 30 seconds to the local cache, then disconnects and starts to shovel something, create excel files, etc. and so on for half an hour.


But blame, of course, SQL Server. :-)))
Re: SQL Server Performance QUESTION! [new]
Mind

in my case there is no such thing, nothing but sql on the server is spinning
Re: SQL Server Performance QUESTION! [new]
dezhnevo
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:

Server:

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.

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 :-)

Note:
:-) theoretically controversial questions are marked, which, however, in 90 +% of cases work exactly as I said :-)
Re: SQL Server Performance QUESTION! [new]
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.
Thanks again.
Re: SQL Server Performance QUESTION! [new]
uaggster
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.

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.
Re: SQL Server Performance QUESTION! [new]
dezhnevo
growth rate I put 10%
imho, percentage increase = possible shot in the foot in the future, the generally accepted opinion for a long time, despite the default settings MS?!
Re: SQL Server Performance QUESTION! [new]
dezhnevo
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.

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 :-)
dezhnevo
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?

The percentage increase in files to use EXTREMELY is not recommended.
Set a fixed gain.
dezhnevo
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.
Thanks again.

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.
Re: SQL Server Performance QUESTION! [new]
Andy_OLAP
uaggster
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.

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.
[/ quot]
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.
Andy_OLAP
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.

Why? Logs on another disk.
Well, I'll save the tail of the log. Or I will back it up more often.
Re: SQL Server Performance QUESTION! [new]
uaggster
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.

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 .
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
uaggster
dezhnevo
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."

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 this increase :-)
Most of the above with a 95% probability will not affect the “slows down, does not work well”. As a result, a lot of work, but little sense. For example, tezhe 12 files under tempdb. More than 8 it makes sense to create only if there is tempdb contention. In short, the quality of training is certainly good, but the problem of the vehicle will most likely not solve, well, except for paragraph 23 :-)
Re: SQL Server Performance QUESTION! [new]
uaggster
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.
and out of 8 kb, only 2 kb is useful there, the rest is marked as deleted.
This is only possible with mass deletions, because inserts and updates divide the page into a hitch, so that below 50% of the used space is still very rarely dropped.
Re: SQL Server Performance QUESTION! [new]
uaggster
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/


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?
Re: SQL Server Performance QUESTION! [new]
For the rest of the points, also a finger to the sky. What if it already is? Then what do you advise?
Re: SQL Server Performance QUESTION! [new]
Relic hunter
uaggster
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/


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?

Why on one? On the mirror.
Well, watch out for their condition :-)
Re: SQL Server Performance QUESTION! [new]
Relic hunter
For the rest of the points, also a finger to the sky. What if it already is? Then what do you advise?

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"?
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
uaggster
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.
Re: SQL Server Performance QUESTION! [new]
Mifodya
uaggster
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. :-)))
Re: SQL Server Performance QUESTION! [new]
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.
Re: SQL Server Performance QUESTION! [new]
Relic hunter
Did you know that the bandwidth of the SSD - nikakuschy. Here the more spindles the better.
"Bandwidth" is a characteristic of the interface, not a storage method.
Relic hunter
Here the more spindles the better.
I will tell you a terrible secret - SSD can be used more than one at a time. And collect raids from them. And to do everything the same as with ordinary disks, if only the controller could.
Re: SQL Server Performance QUESTION! [new]
Vladislav Kolosov
In my practice, there have been cases when moving bases on an SSD has degraded performance. But what is the reason, I did not find out.

The SSD is not all right with the performance beyond the SLC cache.
OSZ
Some SSDs, especially low-end models on TLC flash memory like the OCZ Trion 100, use the SLC cache to speed up the write process. This method allows to use part of the capacity as SLC (1bit-per-cell) NAND flash memory. When writing, only one bit is written instead of 3, thereby significantly increasing the speed of the operation. When the SLC cache is full, it will be transferred to an unoccupied volume, freeing up space for further work of this method to speed up performance. Under normal load, the user does not notice this transfer. But with an increased load, a temporary decrease in the speed of the drive is possible, when the cache is already full, but the write operations have not yet completed. After transferring the cache to the free space of the drive, the speed will be restored.

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.
IMHO so.
:-)
Re: SQL Server Performance QUESTION! [new]
uaggster
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"?
First you need to find out why it slows down. If all resources (percents, disks, memory) are normal, then almost certainly these are curve / non-optimized queries. Well, as has already been said 25 times, you must first find out from users what it is slowing down, otherwise you can search for the wrong place.
Re: SQL Server Performance QUESTION! [new]
Mind
uaggster
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"?
First you need to find out why it slows down. If all resources (percents, disks, memory) are normal, then almost certainly these are curve / non-optimized queries. Well, as has already been said 25 times, you must first find out from users what it is slowing down, otherwise you can search for the wrong place.

I have no more questions.



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ