Go to Main page..

Question on tempDB , Microsoft SQL Server





Question on tempDB [new]
Hello.
There was a question about upgrading the server and installing pci ssd cards.
Now it is by default on C: and it weighs 30 Gb.
The question is whether to transfer it to a fast disk, since it is a random, quick reading, and not a sequential one.

Just a question on the number of tempDB, now one file. Holivar on how many of them should be, I understand, you need something like bestpractice. That is, what to build on, the number of processors, cores, the number of bases, etc.
Thanks, be happy to help with advice.
Re: Question by tempDB [new]
dezhnevo

there is always meaning, it will not be worse. advice from yourself - do not keep the temp base on the system disk with Windows.
especially if tempdb is growing very fast.
Regarding the number of files tempdb - build on the number of processors.
in nete a lot of information on this subject.
Re: Question by tempDB [new]
NeGuruSql,

Thanks for the advice, I will be glad if someone else will share :)
Re: Question by tempDB [new]
TempDB is very desirable to place on a separate SSD.
Separate, so that "suddenly" does not occupy the entire drive C and drop the server.
Especially true if time is tightly used. tables (for example in 1C).
Re: Question by tempDB [new]
L_argo,

Yes, apparently we will. thank
Re: Question by tempDB [new]
question about temp files
there is an opinion that for each server you need as many tempdb files as there are cores in the system
Re: Question by tempDB [new]
who will say the right opinion?
Re: Question by tempDB [new]
I do not quite understand the dependence of the number of physical nuclei percent. server number of files. Especially, if the file (or files) will be located on a high-speed separate pci ssd (for example, INTEL SSD DC P3520 SERIES)
Re: Question by tempDB [new]
dezhnevo
I do not quite understand the dependence of the number of physical nuclei percent. server number of files.
https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
Re: Question by tempDB [new]
Increase your data structures.
It is less than or equal to eight (8). (8), use eight data files. If the contender continues to increase its number Alternatively, make changes to the workload or code.
Re: Question by tempDB [new]
archivist
Increase your data structures.
It is less than or equal to eight (8). (8), use eight data files. If the contender continues to increase its number Alternatively, make changes to the workload or code.
Not sure if this is true for SSD.
Somewhere here was a link to a habour article cat. questions the expediency of breaking TempDB into pieces.
Re: Question by tempDB [new]
invm
I do not quite understand the dependence of the number of physical nuclei percent. server number of files.
https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/ [/ quot]

You cite as an example an article more than five years old. I'm not to the fact that the data is outdated, but to the fact that SSD drives appeared that level any delays in selective reading. Well, for example, that SSD that I gave as an example, about 400 thousand IOPS for reading. Well, what are the brakes of iron. And accordingly, the question is why split the file with an eye on the processor. By monitoring, I see that the processor is optimally loaded, nothing more.
I would be glad to give an example of the type: "my server was slowing down, I found out that the reason was in tempDB, I broke it up into several, as advised by the formula, the performance has greatly improved." Here is an example, personal experience, "best praktis".
Re: Question by tempDB [new]
dezhnevo

split up so that parallel IO processes do not hang on one core and do not create a bottle neck. In addition to iron, there is also an operating system.
Re: Question by tempDB [new]
Vladislav Kolosov,

Operating system? So what? it is completely unloaded, there is nothing but SQL. Neither antivirus nor the Internet. Nothing! What can it consume globally? SQL generally stands alone, it is essentially a separate system from the OS that is spinning on its own
Re: Question by tempDB [new]
dezhnevo
You cite as an example an article more than five years old. I'm not to the fact that the data is outdated, but to the fact that SSD drives appeared that level any delays in selective reading.
And what have the SSD-disks, if the problem described in the articles arises with access to pages in memory?
Re: Question by tempDB [new]
Gavrilenko Sergey Alekseevich,

To the fact that if the memory fails, the pages are "flushed" to disk.
Re: Question by tempDB [new]
dezhnevo
Gavrilenko Sergey Alekseevich,

To the fact that if the memory fails, the pages are "flushed" to disk.
Would you first ask when, how and in what order data is reset from memory.
Re: Question by tempDB [new]
dezhnevo
You cite as an example an article more than five years old. I'm not to the fact that the data is outdated, but to the fact that SSD drives appeared that level any delays in selective reading.
First, read what PAGELATCH_ * from PAGEIOLATCH_ * do, then go back to the study of the proposed article.
Re: Question by tempDB [new]
invm,

Well thank you. But now I see that at peak the load on tempdb is more than 100%. Need to collect more data
Re: Question by tempDB [new]
dezhnevo

For "normal operation", what is the average load on each tempdb file? Less than 80% 90%?
Re: Question by tempDB [new]
dezhnevo

the question of how the operating system accesses the file for writing and which locks it stops.
Re: Question by tempDB [new]
dezhnevo
dezhnevo

For "normal operation", what is the average load on each tempdb file? Less than 80% 90%?
https://www.google.com/search?q=sql server tempdb bottleneck & rlz = 1C1GCEU_enRU821RU823 & oq = mssql tempdb bottl & aqs = chrome.1.69i57j0.10956j0j7 & sourceid = chrome & ie = UTF-8
Re: Question by tempDB [new]
Here is such a "funny picture" for a short period of time.

Attached file Size - 56Kb
Re: Question by tempDB [new]
dezhnevo
You cite as an example an article more than five years old. I'm not to the fact that the data is outdated, but to the fact that SSD drives appeared that level any delays in selective reading.
What kind of people went, even up to the 3rd line can not read.
Paul Randal
Tempdb contention refers to in-memory; I / O.

dezhnevo
I will be glad if someone else will share :)
But does it make sense?
Re: Question by tempDB [new]
dezhnevo
invm,

Well thank you. But now I see that at peak the load on tempdb is more than 100%. Need to collect more data
More than 100% of what? Maybe I don’t understand what, but this one of yours shows measurements in parrots.
Re: Question by tempDB [new]
Mind
dezhnevo
invm,

Well thank you. But now I see that at peak the load on tempdb is more than 100%. Need to collect more data
More than 100% of what? Maybe I do not understand what, but this is your tul shows measurements in parrots.


also amused picture
especially peaks under 200%
Re: Question by tempDB [new]
komrad
Mind
skipped ...
More than 100% of what? Maybe I do not understand what, but this is your tul shows measurements in parrots.


also amused picture
especially peaks under 200%

Here is such a Grafana and hiding one of the two indicators on the chart .
Re: Question by tempDB [new]
Andy_OLAP
komrad
also amused picture
especially peaks under 200%

Here is such a Grafana and hiding one of the two indicators on the chart .
The person should explain the schedule somehow.
Otherwise, I don’t understand what his words “This is such a funny picture” means, maybe you need to worry when “tempdb workload” exceeds 10,000%?
Re: Question by tempDB [new]
Mind

Breathe deeply
Re: Question by tempDB [new]
Andy_OLAP,

Like that, but all is simpler, limited min and poppy indicator of the chart 0-100%
the chart directly removes the metric from the server using the perf_counter ["\ SQLServer: Databases (tempdb) \ Transactions / sec"] key
Re: Question by tempDB [new]
dezhnevo

it's just the beginning of the day

Attached file Size - 96Kb
Re: Question by tempDB [new]
dezhnevo
Transactions / sec
That is, interest is just the wrong signature of the axis of the graph?
Re: Question by tempDB [new]
alexeyvg,

true, there is a maximum on the chart and min. respectively. 100% taken over the ceiling, so if it piles on 100%, then overload.
Re: Question by tempDB [new]
According to the schedule, some kind of zagulyanny procedure is clearly figuring. Not ?
Re: Question by tempDB [new]
dezhnevo
alexeyvg,

true, there is a maximum on the chart and min. respectively. 100% taken over the ceiling, so if it piles on 100%, then overload.
And that's it, the measured values ​​are in the range of max. to min.
Interesting.
Re: Question by tempDB [new]
L_argo
According to the schedule, some kind of zagulyanny procedure is clearly figuring. Not ?
Rather, the user is somehow.

There is no periodicity, so that, for example, it would trigger once every half hour. So this is not a sheduler.
Re: Question by tempDB [new]
alexeyvg,

The graph essentially shows the load on the counter, that is, measurements can be specified in anything, even in liters per second. Only peak values ​​are important. Everything is also correlated with the network load, users got into the database. I will watch. Thank.
PS this is how the removal of the main indicators. True, this is not all that fit the screen

Attached file Size - 96Kb
Re: Question by tempDB [new]
dezhnevo
The graph essentially shows the load on the counter, that is, measurements can be specified in anything, even in liters per second. Only peak values ​​are important.
I do not understand what you want to say.

The load is not shown "in liters".
In liters, the volume, power in watts, transactions per second in units per second, intersity of disk operations in units per second, intersivity of writing to disk in bytes per second are shown.

The percentage describes only the relationship. For example, 100% is 1, 80% is 4/5
One might think that this is a disk load, but you write that this is "\ SQLServer: Databases (tempdb) \ Transactions / sec"
And if the graph is more than 100%, then this is generally incomprehensible, then it’s just not percentages, and liters or angström.

Here you publish the graph in liters, and ask questions, such as "the load on the tempdb of 18 liters is a lot or a little?"
I’m probably just stupid and I don’t understand anything. And it seems that everything here, except you, stupid, can not enter.
Re: Question by tempDB [new]
alexeyvg,

My fault, not the screenshot attached. put exactly the pieces (transactions) per second

Attached file Size - 136Kb
Re: Question by tempDB [new]
alexeyvg
I’m probably just stupid and I don’t understand anything .. And it seems that everything here, except you, stupid, cannot enter in any way.
It's just that the author's method of the TS - he firmly believes that the indicator "\ SQLServer: Databases (tempdb) \ Transactions / sec" correlates with the load on the disks and the number of tempdb data files.
Re: Question by tempDB [new]
invm,

:)
Re: Question by tempDB [new]
invm,

Any data can be represented as a standard graph 0-100%. There is always the maximum value for you and there is a minimum (most often 0). Or do you disagree? I do not need, for example, to know the number of transactions (the amount per second), I set the threshold of values ​​and look at the excess.
Re: Question by tempDB [new]
dezhnevo
invm,

Any data can be represented as a standard graph 0-100%. There is always the maximum value for you and there is a minimum (most often 0). Or do you disagree? I do not need, for example, to know the number of transactions (the amount per second), I set the threshold of values ​​and look at the excess.
And any data can be divided into 666 and called parrots. The enemy will not exactly see what it is about.
Re: Question by tempDB [new]
Gavrilenko Sergey Alekseevich,

You have a system, you take readings from it, many readings. And these readings have large numerical values ​​and there are many of them (even if it’s not even about SQL). You need to create a load schedule to track the viability of the system, how will you do it? Well, if not difficult.
Re: Question by tempDB [new]
dezhnevo
Any data can be represented as a standard graph 0-100%. There is always the maximum value for you and there is a minimum (most often 0). Or do you disagree?

Well, of course, any value fits in the range from 0 to 100%, why show the graph, it’s understandable to everyone. On any system, the load on tempdb will vary from the minimum to the maximum observed.
dezhnevo
Gavrilenko Sergey Alekseevich,

You have a system, you take readings from it, many readings. And these readings have large numerical values ​​and there are many of them (even if it’s not even about SQL). You need to create a load schedule to track the viability of the system, how will you do it? Well, if not difficult.
You said everything correctly, only such graphics carry information exclusively to you, because only you know what lies behind these parrots.

And it’s useless for other people to show them, because they don’t know the unit “parrot”, it is known only to you, and even harmful, because it confuses them - so we spent two days in this thread finding out.
Re: Question by tempDB [new]
Gavrilenko Sergey Alekseevich,

Sergey Alekseevich, here is the article https://www.mssqltips.com/sqlservertip/1853/sql-server-tempdb-usage-and-bottlenecks-tracked-with-extended-events/ you give it as an example, as one of the options. The main question remained - HOW to determine that the existing tempdb is a bottleneck? The person in the comments on the article is also interested. That is, I have data, OK, a lot of data, I see everything, BUT, reliably determine that the problem in the non-optimal operation of the server is in excessive workload tempdb HOW? that is, it is some kind of scholastic argument.
Re: Question by tempDB [new]
dezhnevo
My fault, not the screenshot attached. put exactly the pieces (transactions) per second
Well, on this screen you can already see what the load on tempdb
In general, nothing special, indicators as indicators.
Without knowledge of your system, of course, they remain normal numbers.
For a classic HDD 50 iops, this is already decent, some kind of SSD would be necessary, even if not a PCIe
Re: Question by tempDB [new]
dezhnevo
The main question remained - HOW to determine that the existing tempdb is a bottleneck?
See the queue to drive.
This is a good quick test.
If the queue is 0.1 or less, then nothing to worry about.
If closer to 1, then you need to pay attention.
If more than 1 (2,3,10 ...), then the disk system does not cope.
In fact, for raids, the criteria are slightly different, but for quick evaluation it will do.
Re: Question by tempDB [new]
alexeyvg,

Thanks, these are already recommendations that are valuable. understand, I'm not a super expert in the field of SQL server, I'm just learning :)
Re: Question by tempDB [new]
dezhnevo
invm,

Any data can be represented as a standard graph 0-100%. There is always the maximum value for you and there is a minimum (most often 0).
Did you miss school?
The maximum allowable value for you personally may have no logic. 0-100% makes sense only if there is a clear definition of this 100% limit. For example, you have a bottle on the floor of a liter; Indicators such as Transactions / sec do not have a clear limit. Let's say it can be determined for your specific system, but for this you need to artificially create the maximum possible number of transactions and when the server starts to shut up and the counter stops growing, this will be your 100%. And even in this case there will not be much sense from this. Because where exactly bottleneck won't tell you that. Due to the absolute impracticality and senselessness of this approach, no one monitors Transactions / sec as a separate counter. Together with the others it is possible, but in fact only to understand what is happening at this moment in the system, and not why everything is slow.
Re: Question by tempDB [new]
dezhnevo

read the description of each meter and think about - what are its indications are critical, and which are not for your system. Counters are simply status indicators. Knowledge of the values ​​is a necessary but not sufficient condition. To understand their meaning, you need to know and understand the system configuration.
Re: Question by tempDB [new]
Vladislav Kolosov,

Thank you, I do.
Re: Question by tempDB [new]
Mind

School did not miss. Thank you for your meaningful comments.
Re: Question by tempDB [new]
Dear comrades from a respected office have recently told us that everything is bad with the server because "critical blood buffer ratio, full scans per sec and especially database transactions per second". And they asked for sysadmin rights so they could fix it. Laughed all the department.
Seeing 100 transactions per second is the limit for modern DMCs.
Re: Question by tempDB [new]
The screen did not attach.

Attached file Size - 141Kb
Re: Question by tempDB [new]
Mind

Do you not see with a sight? The current readings are shown in the counter. 0-100 is a graphical representation of this plugin, which, apparently, is static, except for the arrow. But, I think you yourself saw it. I just really wanted to control it. 5+
Re: Question by tempDB [new]
dezhnevo
Mind

Do you not see with a sight? The current readings are shown in the counter. 0-100 is a graphical representation of this plugin, which, apparently, is static, except for the arrow. But, I think you yourself saw it. I just really wanted to control it. 5+
Eeee, what have the pictures and the "Current readings" ???
Absuden is the lowest level of consultants, what they said. What does it matter what's in these pictures?
Re: Question by tempDB [new]
alexeyvg,

Clear. I agree. They did not inspect something. Everything in 100%, of course, is not correct to measure :)
Re: Question by tempDB [new]
dezhnevo
alexeyvg,

Clear. I agree. They did not inspect something. Everything in 100%, of course, is not correct to measure :)
What does the interest?

They wrote that "critical levels are buffer cache hit ratio, full scans per sec and especially database transactions per second", while buffer cache hit ratio is 100% envy for everyone, and by the number of database transactions per second nothing can be said, no matter what there was no figure.

Yes, they didn’t explain their analysis, but asked to let them dig deeper, never answering.
Re: Question by tempDB [new]
alexeyvg
dezhnevo
alexeyvg,

Clear. I agree. They did not inspect something. Everything in 100%, of course, is not correct to measure :)
What does the interest?

They wrote that "critical levels are buffer cache hit ratio, full scans per sec and especially database transactions per second", while buffer cache hit ratio is 100% envy for everyone, and by the number of database transactions per second nothing can be said, no matter what there was no figure.

Yes, they didn’t explain their analysis, but asked to let them dig deeper, never answering.
Well, this is just a classic, when a client calls in the support that the application does not work well, then the answer is usually your DBAs that are crooked, they cannot configure the SQL server, you can see that the number of transactions is off scale! Like that.
By the way, they did an "analysis". We looked at errorlog, found 1 deadloack there, now they’re writing a report, after all, they’ll probably send it.
Re: Question by tempDB [new]
Mind

By the way, they did an "analysis". We looked at errorlog, found 1 deadloack there , now they’re writing a report, after all, they’ll probably send it.

1204 or 1222?
not easier from ring buffer?
Re: Question by tempDB [new]
One deadlock is harsh :) And if there are several dozen per hour and the system works stably at the same time?
Re: Question by tempDB [new]
Vladislav Kolosov
One deadlock is harsh :) And if there are several dozen per hour and the system works stably at the same time?

This means that the developers came up with a full understanding that the database is an enemy object that seeks to respond to any request either with a deadlock or with a timeout . Which needs to be forced from the next attempt to accept and fix the data. Which resists and tends to remain in a static state.
And such programs become successful. And in others - there are always regular bugs.
Re: Question by tempDB [new]
[alexotvg quot]
dezhnevo
alexeyvg,

They wrote that "critical levels are buffer cache hit ratio, full scans per sec and especially database transactions per second", while buffer cache hit ratio is 100% envy for everyone, and by the number of database transactions per second nothing can be said, no matter what there was no figure.

Yes, they didn’t explain their analysis, but asked to let them dig deeper, never answering.


Attached file Size - 8Kb
Re: Question by tempDB [new]
dezhnevo

everything is lost :)
Re: Question by tempDB [new]
komrad
Mind

By the way, they did an "analysis". We looked at errorlog, found 1 deadloack there , now they’re writing a report, after all, they’ll probably send it.

1204 or 1222?
not easier from ring buffer?
I don't think they heard about ring buffer.



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ