Sql-server – the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this

sql server

I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following

Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads
Disk reads might also improve if your table spaces are partitioned.
You will be able to clearly segregate database objects into separate physical locations (tables/clustered indexes/etc) Although I am not sure how this exactly works and how this might help improve the performance of the database

Now I'm not really sure if these assumptions of mine are correct and if so, what are the other advantages aside from what I have mentioned?

On the other hand, are there any quantitative measures that I can use in order to design my custom table spaces? For example, is there any mathematical formulas that I can use in order to set the growth size of table spaces according to the table's number of columns? Or is it more of predicting growth based on a timeline of gathering statistics of the table's usage?

One last thing, Is it much more efficient to segregate table spaces according to database object type? or according to logical design? (e.g. create separate table spaces for indexes and tables or create separate table spaces for each business module/function)

Please take note that I still have little experience with this and feel free to ask for any clarification if my question/s are not understandable

Edit: So I've read a bit about the mdf and ldf files and it seems to slightly address my problem with the growth of the database.

source: http://www.simple-talk.com/content/article.aspx?article=920

but still, Is there any importance of segregating per table space?

Edit 2: Okay based on further reading, if I am not mistaken, this basically falls on the category of segregating into different files/filegroups.. What advantages can I also expect from this aside from having to control the growth of the different mdf/ldf files, instead of maintaining just one big file?

Best Answer

A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.

Related Solutions

Sql-server – tempdb on raid1 isolated or raid10 shared with high write db

I found these articles helpful:

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1. Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

Sql-server – How to configure SQL Server 2012 so that it can restore and see files in the user account

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.

To configure file system permissions with SQL Server you can follow the instructions from MSDN here.

The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.

Virtual Accounts

Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format \$. When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see Register a Service Principal Name for Kerberos Connections. Note Note Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

The following table lists examples of virtual account names.

Default instance of the Database Engine service: NT SERVICE\MSSQLSERVER Named instance of a Database Engine service named PAYROLL: NT SERVICE\MSSQL$PAYROLL SQL Server Agent service on the default instance of SQL Server: NT SERVICE\SQLSERVERAGENT SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICE\SQLAGENT$PAYROLL

Best Answer

Related Solutions

Sql-server – tempdb on raid1 isolated or raid10 shared with high write db

Sql-server – How to configure SQL Server 2012 so that it can restore and see files in the user account

Related Question