Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 298 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
How does SQL Server store data?
einsTeIn.NET
Member Posts: 1,050
Hi guys,
We've got some performance problems with two of our databases. They are one the same SQL Server and they are both distributed on the same four disks. I checked disk space for these disks and I noticed that the database is unequally distributed. One database part has grown to 14GB and another part is almost just 7GB. What is the reason for this?
File Growth is set to 35% for all parts. That means for one disk there is not enough free disk space to grow again by 35%. Could this be the reason for the performance problems even though there is enough free disk space on the other disks? What would you suggest for a restructuring task, should it be a one time job or recurring?
In general, how does SQL Server store data?
All the best for 2011!
We've got some performance problems with two of our databases. They are one the same SQL Server and they are both distributed on the same four disks. I checked disk space for these disks and I noticed that the database is unequally distributed. One database part has grown to 14GB and another part is almost just 7GB. What is the reason for this?
File Growth is set to 35% for all parts. That means for one disk there is not enough free disk space to grow again by 35%. Could this be the reason for the performance problems even though there is enough free disk space on the other disks? What would you suggest for a restructuring task, should it be a one time job or recurring?
In general, how does SQL Server store data?
All the best for 2011!
"Money is likewise the greatest chance and the greatest scourge of mankind."
0
Comments
yes, there can be load balancing problem, because sql tries to write on one disk only (because one of them is full) instead of 2 (or 3 or 4...it depends). But i'd like some expert to confirm this.
to you too!
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
You write about 2 DBs but in the next sentence about 2 DB files. What is the setup exactly? 2 files from 1 DB? Is it then 1 mdb and 2 ndbs? Or are you talking about 1 mdb and 1 ndb?
Usually the mdf is setup with 1GB and never grows. It holds only the objects unless you use some tools which write into the mdf like the SQL Perform Tools. The only part which will grow is the ndf (here is the data). You could explicitly setup the DB with more then 1 ndf but with only 20GBs of data that is overkill.
Having 2 DB files on the same set of spindles is never a good idea (if it is from one DB 2 files or from 2 DBs one file each does not matter). Probably the disks are setup as Raid 10 with 2 LUNs?
:shock: [-X
If one part is 14GB and it can not grow by 35% (=4,9GB) then your LUN has less then 18,9 GB? :-k What is this? A Raid 10 out of 4 18GB drives with 2 LUNs (18GB each?)
If you have 2 ndfs (which I doubt):
SQL Server will distribute the data evenly over the 2 parts until they are full and will then expand first one part and then the second one.
During the file growth all processes reading from or writing to the DB are blocked. That is also why you should not set up automatic file growth (and if you do so then definitely not with 35% as it takes much too much time).
Let us have MUCH more information or get a SQL Expert in who can help you.
FD Consulting
Yes, that's correct. We've got multiple databases. Two of them are setup in the same way on the same server. Each of these two database consist of one primary database part (mdf) and four secondary database parts (ndf).
Yes, of course, primary database part does never grow.
This was a suggestion by a very famous solution center which is well known for their SQL knowledge.
No, as I explained above, it's not the only database on this LUN. And yes, we are very indigent.
No, as I already said we have four for each database.
As far as I can see our SQL Server does not do it in this way. Maybe there are some settings wrong.
Of course, I know. I didn't say that automatic file groth is activated.
The current system design is the result of the expert's suggestion we got in.
How much space is free in the 4 files? How much space is left on the LUNs? Couldn't you just expand manually by another factor?
Which is probably from a time when hardware expansion was out of the question. So the solution at that time was to split DB files and do Index Optimisation. With your old hardware it was probably a good idea to have several files per DB.
From what you told I assume 5-6 years old hardware (18GB disks are not available since a long long time). You said 2 DBs (each 4 files) distributed over the same 4 disks. I think you should give us a complete picture over your setup so that we can be of better help.
Coming back to your original question: As far as I remember SQL stores data from a single table always in the same file (at least if you have one filegroup which is the default with NAV) and the file is chosen at the time of creation of the table. If you want to separate hot tables over files you would have to create new filegroups and change the tables manually in SQL. Has this been implemented in your DB by the SQL Expert? This could explain the unevenly distributed data.
As Belias wrote, it could be a load balancing problem. It could be a lot of other things.
FD Consulting
FD Consulting
Provided that the disk system can handle the I/O demand. Doesn't matter if you have 1 DB file or 100 DB files on the same disk array.
Transaction log files are a different story. They should each have their own dedicated disk.
Don't think I agree with either of these statements. NAV database, that I spend a good part of my time with, is 380 GB (320 GB used). The default file group consist of 3 data files. Each placed on its own RAID 10 array. If I check the used space on these files they are all within .05 GB of each other. The system seems to be spreading the data fairly evenly across them.
:thumbsup:
This is the only correct answer.
1. ndf 2. ndf 3. ndf 4. ndf mdf Yes, it's possible to expand manually. But first of all I need to know if that is the reason for those performance problems.
That is what I've also heard. That's why I ask "How does SQL Server store data?". If that's the case then the performance problems are maybe related to the situation of the first database part (ndf). If that's not true then I wonder why all database parts have a different size.
First off all this database we are talking about is ~47GB. Secondly you have to consider this is one of our live databases. We can't just play around and switch this and switch that. We need to know where the problem is related to and change exactly this part.
Unfortunately our system doesn't. Maybe we wouldn't run into these problems if it would do so!?
I was wondering what does happen if a disk is full: is the table forced to be splitted in another filegroup?
BTW, your disks are not full :-k
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
My comment is still applicable.
What is the physical configurations of each of these disk. If you mentioned it earlier I did not see it? What is RAID type, disk type/size/speed, and interface?
Also what sort of performance issues are you having? While disk systems may be a contributing factor, I have my doubts they are they only issue.
Where is the transacation log?
I don't know it by heart. I will check it.
NAV is getting slower and slower. I guess it starts two weeks ago. SQL Server process needs up to 80% of the server's power (quad core X5355 2.66GHz, 12GB RAM, Win 2k3 server SP2).
On another disk.
unless you can post some basic details about your server configuration, it wont be possible to help you.
My statement goes more to "why was this done in the first place". Not to suggest that switching back will solve all your issues. As I said earlier, I'm not clear that the disk is even your issue.
With that high of CPU, I suspect that there are indexing and statistics issues with this DB. What sort of maintenance is being done on a regular basis.
On all these files you mention the size, max size, and free disk space. But you don't mention how much free space (or used) within the file.
Check in SSMS Activity Monitor the processes with highest CPU demand - is this something else than a NAV client or NAS?
Take a look at this doc, page 6: http://www.stryk.info/Performance%20Checklists%201.08.pdf
You could also enter the numbers directly here: http://www.stryk.info/performance_check.html
Get at least the numbers for
Memory
Physical Disks (Avg. Read/Write Queue Length, for all drives with DB files, TLOG and TempDB)
Network
Process
SQL Server Access Methods
SQL Server Buffer Manager
SQL Server Locks
You probably have nothing to compare with. Create the counters in Perfmon and let them run for a while.
FD Consulting
Maybe some brief general advices, in addition (or repeating) what has already been mentioned:
1.
The out-of-the-box db layout (1 x MDF, 1 x NDF, 1 x LDF) is perfect for most databases, presuming the files are properly stored on decend disks and correctly configured:
MDF/NDF on one dedicated drive, RAID10, number of spindles depend on db size, e.g. with 20GB I'd suggest 4 HDD
LDF on one dedicated drive, RAID1 or RAID10 (2 to 4 HDD), depending on transaction volume
THINK SPINDLES, NOT SPACE!
2.
MDF size/growth usually could be ignored.
NDF size should be sufficient enough to avoid frequent Auto Growth as this will physically fragment the file (AG should just happen in case of an emergency). I recommend fixed/linear growth, as with relative growth (%) the added chuncks are getting larger and larger, thus, more and more disk space is wasted. E.g. with 20GB net db size the file size could be 100GB; Auto Growth 1000MB.
The minimum amount of free space within a file should be 1.5 times the size of the largest index, as this is the "worst case" space required for index defragmentation.
LDF size depends on Transaction Volume and frequency of Log Backups; some rule of thumb could be: "LDF size = 20% net DB size"
Autogrowth fixed, e.g. 500MB (same as above: only as an emergency feature)
3. Splitting the DB into multiple NDF files is pointless with such a tiny database. With DBs of 300GB or more it could make sense to implement different DB architectures, and this needs to be thoroughly discussed!
Generally: SQL Server cannot perform a real load balancing; objects will always be stored in one file. Means, a table - e.g. the Clustered Index - will always be in one and the same file. For example, if T32 "Item Ledger Entry" is stored in file #1, it will always remain there. Hence, if you have "pressure" on this table, you have the pressure on one file (#1) - nothing gained!
Splitting into multiple NDF files only makes sence if you could store each file on a dedicated drive to segregate physical I/O, else this is nonsense.
Splitting into multiple LDF files is 100% nonsense - the Log is written purely sequential, thus there cannot be any kind of "parallelism". In some architectures you'll find a second LDF as emergency file, once the primary Log is "overflowing" (IMHO this is nonsense, too)
As long as your current architecture does not match these essential aspects it is impossible to tell where the performance problems are coming from; mabye I/O monitoring with Windows Perfromance Monitor & Co. will give a clue.
You can migrate multiple files back into one file using the DBCC SHRINKFILE command with EMPTYFILE parameter (see "Books Online" about details). Afterwards you must run a complete Index Rebuild task!
My two cents.
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
The disk sizes concern me.
How do you get 13+31 = 44gig hard drive? Drives are 36 or 72 gig.
To me this looks like the Log (and probably the DB) are actually on some virtualized drives, and not on dedicated spindles.
Precisely why I asked for the physical details.
My general question was "How does SQL Server store data?". I only looked for an answer if this database file situation could be an issue. And I'm interested in how it works exactly in background. I don't expect you to solve all my problems. I know that it's quite impossible to solve such problems from somewhere else in the world with only a rough overview of the system.
Some members asked about our system configuration, so I answered. If someone is out there is able to give a useful hint to solve it I would be very thankful and happy. But I don't expect anyone in this forum to be the substitute for an external SQL expert for our current situation.
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.
:-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.
This is sort of like not changing the oil in your car. It runs along just fine, day after day. Then one day it just stops running. Not because of soemthing you did today, but something you haven't been doing all along.
Did your SQL Expert provide any explanation of why these recommendations were made?
](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)