SQL Server Management Studio
Index and Statistics
The course is part of this learning path
Information is at the heart of most software systems and the lifeblood of many organizations, so you want the database that stores this information to be efficient and reliable. But as we know, things happen; sometimes bad things. One of the ways that we can prevent bad things from happening is to know about them in advance like the old saying says, "To be forewarned is to be forearmed."
Azure SQL in its many forms has an abundance of features that help you to monitor the state of your databases and database server. Ranging from prebuilt automated monitoring that is augmented with artificial intelligence through to dynamic management views, SQL Server monitors and logs all aspects of the database engine’s operation and configuration. Intelligent Insights and Azure SQL analytics enable you to easily access the wealth of diagnostic and performance data in an easily digestible format.
This course introduces you to the different database monitoring and notification technologies available, how they work, and how to use them. If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Understand the key elements of database monitoring
- Learn about the features of Intelligent Insights, Azure's AI-based database monitoring service
- Create graphical reports using SQL Server Management Studio
- Understand how wait statistics can show you where threads have to wait and how this can be used to monitor performance
- View and fix index fragmentation
- Monitor database storage
- Implement notification alerts on various database platforms
This course is aimed at database administrators or anyone who wants to learn how to implement systems that can find potential issues that may disrupt the delivery of their database services.
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
Monitoring the size of your database is one of the most important aspects of database administration. There are three storage sizes that come into play here. The maximum possible size, the currently allocated size, and how much is being used to store data.
The maximum size is the size that a database file can grow to. This is generally limited to the maximum allowed by the service level tier that you're using, or the physical size of the partition that your file is stored on. The Azure SQL database data storage graphic tells us that the maximum that our data file can grow to is 250 gigabytes. There is no point in allocating all 250 before it is needed, so only 2.64 gigabytes have been allocated of which only 2.4 gigabytes are being used.
These storage numbers only apply to the data file, that is the database's log file is not included in these numbers. On the other hand, looking at the property page of an SQL Server database we can see that the file type of rows, which is the data only storage, similar to the Azure graphic has an unlimited maximum size.
This database's allocated size will grow automatically in 64-megabyte chunks until the disk is full. However, the log file will only grow to a maximum size of 20 gigabytes. The allocated size of the database file is the actual file size when you view it within the computer's file system, so how much physical disk space is being taken up by the file.
Azure SQL databases use auto-growth so that the amount of disk space used is only as much as is required for the data being stored. In what is essentially a shared environment this makes a lot of sense, and the amount that and Azure database grows by can be viewed by querying sys.database_files.
While auto growth on the face of it is a good thing in that you manually don't have to increase the file size, there are some potential downsides. Every time the database needs to grow, which is in response to more data being added, the server has to take time out from processing queries to grow the file.
If the auto-growth amount specified is too small then the server will spend too much time allocating file space, and you will also end up with a file that is far more fragmented at the hard drive level, which will also impede performance.
One alternative is to set up a database without auto-growth, which can be done when you are specifying the data files, or you can specify a grow by size that will reduce the number of growth events. You may also want to change the auto-growth size temporarily, say when you are importing a very large dataset.
An Azure SQL managed instance database defaults to an auto-growth size of 16 megabytes. You could use the alter database command with modify file and the data files logical name specifying the file growth size. This value could be set to several hundred megabytes for the duration of the data import and then set back to the original production size.
It will come as no surprise that there is an Auto Shrink feature to go with auto grow. While Auto Shrink may be useful in terms of administration effort, Microsoft does say that it is not quite as effective at reclaiming disc space. Within the SQL Server community, the use of Auto Shrink is frowned upon, to put it mildly, and its use is very much discouraged due to performance degradation caused by it increasing index and file fragmentation.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.