How does SQL Server store data?

FDickschat

einsTeIn.NET wrote:

Why could indexing and statistics be an issue after a while? I mean this system behaviour started without any warning, it just occured one day.

That is how it always starts. One day everything is fine, the next day - kaboom.
If the reindexing job stopped working your statistics become outdated. The server can live with it for some time but after a while the server will revert to table scans which will increase esp. read IOs, CPU and TempDB usage drastically.

einsTeIn.NET wrote:

:-k I don't understand. 98% of the whole database is used. But I don't know how this value is calculated. And I thought that is quite usual for SQL.

Before you said that DB size is increased manually by 35% - how could it be that the DB is at 98%. To check free size per file: SSMS, Tasks, Shrink File. Choose the files separately without actually shrinking them. The wizard will show the free space in the files.

einsTeIn.NET wrote:

Yes, all of them are virtual drives.

:shock:

stryk

Hmm ... this is bad.

You have to imagine this:
If you use virtual drives, this means all physical drives - the HDD, the spindles - are probably configured in one single array; the virtual drives are just a logical thing, like disk partitions.
So for example, if a query causes the Server reading data from the disks, this keeps the spindles busy. Now if another query needs to read other data, then it simply takes time for the spindles for the repositioning, means finding the physical location of the data. That's the major reason why sufficient RAM cache is crucial - the more data could be cached, the less physical I/O is required.
The worst thing is if the Transaction Log is affected by this, as there's no caching involved here; means, the speed of a transaction depends on the response time of the physical disk where the Log is placed. Thus, when something else keeps the disks busy, the transactions are slowed down.
And this is getting worse in a virtual environement, where on top of all this mess the disks are managed by the virtualisation software, which takes even more time ... ](*,)

stryk

einsTeIn.NET wrote:

But I don't expect anyone in this forum to be the substitute for an external SQL expert for our current situation.

Actually in this thread are participating lots of people with plenty of NAV/SQL experience \:D/ - and some of them are making a living of this 8)

stryk

Regarding Indexing and Statistic Maintenance:

Indexes are crucial to help the SQL Server to retrieve the quried data as quick as possible:
Bad indexes = Bad queries = high I/O = Bad performance
Indexes fragment during time - this is natural behaviour. The more fragmented an index is, the longer it takes to read data from it:
Bad indexes = Bad queries = high I/O = Bad performance
Hence, Indexes need to be dfragmented periodically.

Statistics tell the SQL Server which index is the best for a query. If Stats are insufficient, SQL Server makes "wrong decisions", picking insufficient indexes:
Bad Statistics = Bad indexes = Bad queries = high I/O = Bad performance

einsTeIn.NET

FDickschat wrote:

Before you said that DB size is increased manually by 35% - how could it be that the DB is at 98%. To check free size per file: SSMS, Tasks, Shrink File. Choose the files separately without actually shrinking them. The wizard will show the free space in the files.

mdf: 16% free, 1.ndf: 5%, 2.ndf: 7%, 3.ndf: 9%, 4.ndf: 50%.

einsTeIn.NET

stryk wrote:

Hmm ... this is bad.

You have to imagine this:
If you use virtual drives, this means all physical drives - the HDD, the spindles - are probably configured in one single array; the virtual drives are just a logical thing, like disk partitions.
So for example, if a query causes the Server reading data from the disks, this keeps the spindles busy. Now if another query needs to read other data, then it simply takes time for the spindles for the repositioning, means finding the physical location of the data. That's the major reason why sufficient RAM cache is crucial - the more data could be cached, the less physical I/O is required.
The worst thing is if the Transaction Log is affected by this, as there's no caching involved here; means, the speed of a transaction depends on the response time of the physical disk where the Log is placed. Thus, when something else keeps the disks busy, the transactions are slowed down.
And this is getting worse in a virtual environement, where on top of all this mess the disks are managed by the virtualisation software, which takes even more time ... ](*,)

Yeah, I know about this. Our physical drives are really very fast, I don't think that's an issue. I wouldn't expect that an issue based on the physical I/O increases by leaps and bounds, do you?

einsTeIn.NET

stryk wrote:

Actually in this thread are participating lots of people with plenty of NAV/SQL experience \:D/ - and some of them are making a living of this 8)

Yes, I know. And I'm pretty happy to have that much experience in this forum. Thanks for taking part and for all answers so far.

DenSter

einsTeIn.NET wrote:

Our physical drives are really very fast, I don't think that's an issue

Good one if only I had a dollar for every time a customer said this to me

One of my customers was running on a state of the art SAN by a brand that is well known for their speed. The "engineers" for this brand assured my customer that everything was set up to "best practices", and they could not explain the I/O contention that we saw. They said the separation of log and data files was not necessary "because the proprietary RAID configuration is really very fast" and could not be the problem. A while later my customer switched SAN to a different brand and their performance issues practically disappeared.

Not saying that this is your problem, but I am not impressed by someone saying that their disk subsystem is "really very fast". What matters is how it is set up, and from what you are seeing something is not set up correctly.

What you need to do is get an expert into your system and let them actually measure these things. Make sure that this expert has proven experience with NAV on SQL Server, as well as the particular type of virtual environment you are running on. Also get this expert talking with your storage vendor, so they can discuss performance numbers and concerns that arise.

David_Singleton

einsTeIn.NET wrote:

But I don't expect anyone in this forum to be the substitute for an external SQL expert for our current situation.

If you were a first time poster, maybe, but you have given a lot to this community, and therefor deserve some help back. :thumbsup: Of course when someone comes on here and there first post is for urgent help its a bit annoying, but as a respected site member, I for one would think its good if we give you back something.

In my experience, its very rare that one single issue causes significant performance issues, unless something just changed. But in a stable environment, its generally a combination of factors getting slowly worse until they reach a break point. To ge back into smooth running, you need to go back to the original configuration and find out what was wrong from the start, and address each of the issues.

Step one here is to know exactly how the disks are configured, critical here is that the drive your LOG file is on is at the minimum, a RAID 1 made of two physical drives, and that there is NOTHING else on those physical drives. Since you have 44 gig, its quite possible that in reality you have say a 72 gig RAID 1 with a 44gig partition for the log file, then someone has used the remainder for something else.

I once was evaluating a system for a potential client, that thought they had a dedicated drive for the log file on their 100gig nav database. Turns out some IT guy saw the free space and created a partition to use for their Exchange database, for about 400 email accounts. The client refused to acknowledge that that could cause a performance issue.

einsTeIn.NET

DenSter wrote:

einsTeIn.NET wrote:

Our physical drives are really very fast, I don't think that's an issue

Good one if only I had a dollar for every time a customer said this to me

One of my customers was running on a state of the art SAN by a brand that is well known for their speed. The "engineers" for this brand assured my customer that everything was set up to "best practices", and they could not explain the I/O contention that we saw. They said the separation of log and data files was not necessary "because the proprietary RAID configuration is really very fast" and could not be the problem. A while later my customer switched SAN to a different brand and their performance issues practically disappeared.

Not saying that this is your problem, but I am not impressed by someone saying that their disk subsystem is "really very fast". What matters is how it is set up, and from what you are seeing something is not set up correctly.

Yes, for sure, you are absolutely right. Trust is good, control is better. We already started to look at the workload of our physical drives. I can't tell you the exact configuration of our hardware because I'm not responsible for this part. But as far as I can see and what we've discussed internally so far physical drive I/O is not an issue.

DenSter wrote:

What you need to do is get an expert into your system and let them actually measure these things. Make sure that this expert has proven experience with NAV on SQL Server, as well as the particular type of virtual environment you are running on. Also get this expert talking with your storage vendor, so they can discuss performance numbers and concerns that arise.

Yeah, I know. But you know how busy those experts are and so I thought maybe there is a chance to get a slight ad hoc hint from this forum until we'll get an expert in.

einsTeIn.NET

David Singleton wrote:

einsTeIn.NET wrote:

But I don't expect anyone in this forum to be the substitute for an external SQL expert for our current situation.

If you were a first time poster, maybe, but you have given a lot to this community, and therefor deserve some help back. :thumbsup: Of course when someone comes on here and there first post is for urgent help its a bit annoying, but as a respected site member, I for one would think its good if we give you back something.

Ok, good to know. Thanks again, David. I just wanted to say that I know that this forum is of course a major help for most of the problems all around the world. But it can't replace really profound expert's work on every single and special situation.

David Singleton wrote:

In my experience, its very rare that one single issue causes significant performance issues, unless something just changed. But in a stable environment, its generally a combination of factors getting slowly worse until they reach a break point. To ge back into smooth running, you need to go back to the original configuration and find out what was wrong from the start, and address each of the issues.

In my experience it's the same.

David Singleton wrote:

Step one here is to know exactly how the disks are configured, critical here is that the drive your LOG file is on is at the minimum, a RAID 1 made of two physical drives, and that there is NOTHING else on those physical drives. Since you have 44 gig, its quite possible that in reality you have say a 72 gig RAID 1 with a 44gig partition for the log file, then someone has used the remainder for something else.

That's correct. There is another log file of another database on the same disk. They are both 99% empty.

einsTeIn.NET

One additional thing I discovered just a few minutes ago. It seems that the performance is worse in the two major companies of this database. In the smaller companies it seems to be better.

ara3n

I'm sorry but I had to laugh.

David_Singleton

ara3n wrote:

I'm sorry but I had to laugh.

Made me cry actually. #-o

ara3n

einsTeIn.NET wrote:

One additional thing I discovered just a few minutes ago. It seems that the performance is worse in the two major companies of this database. In the smaller companies it seems to be better.

smaller company means, less data to read and manage by sql. Once that company increases in size you'll have the same issue.

einsTeIn.NET

ara3n wrote:

I'm sorry but I had to laugh.

Great post....

It's up to you about what you laugh and about what you cry. Maybe my english isn't as good as yours, thus I explained something wrong or didn't read quite well. Maybe I'm not as much experienced as you, thus I don't know about everything that is mentioned. And maybe I'm not such a big expert like you, thus I possibly just don't understand some things. But at least this is a serious situation for a real company and real employees. I can't see anything to laugh about. Sorry!

ara3n

It was actually bbrown post that I caused me to laugh. It was where he was hitting his head ~10 times at the wall. I cannot give any more info cause other people have already given you suggestion. It's up to you to follow them.

einsTeIn.NET

I didn't notice that he wants to talk about physical drives and their I/O while I was talking about disk space. I didn't follow this principle:

stryk wrote:

THINK SPINDLES, NOT SPACE!

stryk

Reason is to distribute/split the mechnical workload, e.g. the rotation of the spindles and the movement of the disk-read/write-head. The more "worker" (= spindles) you have to share the load, the less workload per worker = the quicker the response times.

That's what Bob and other's already mentioned: the disk space is often quite tempting to be used for other things, but when "thinking spindles" this is a problem ...

stryk

If you problematic transactions or queries depend on the table (company) size, then you have probably a general query problem, mostly related to indexes or statistics or SIFT/VSIFT or cursors or ...

Could mean, that SQL Server starts scanning some of those tables, and with larger tables the problem is just "more visible", also causing higher impact on the disk subsystem ... Hence, the I/O issue is just a symptom, not the cause ...

Did I mention this?
Bad Indexes = Bad Queries = High I/O = Bad Performance

In this case you should use the SQL Profiler to identify those affected queries to get a hint about other potential optimizations (e.g. Index Optimization or Re-Programming, etc.).
See http://dynamicsuser.net/blogs/stryk/archive/2010/05/20/directions-emea-2010-nav-sql-performance-indexes.aspx

David_Singleton

I wrote this blog a few years back. it is a bit out dated (especially the bit about splitting the SQL DB into multiple parts over multiple RAID 1 arrays, which we now know is wrong and it should be one big RAID 10 array), but the principles are still the same.

Why I don’t want my clients to use SANs for Dynamics NAV (Navision)