Please read the Introductory Post before continue reading interview question and answers.
To have a better grip on the concept of Policy-based management, there are some key terms you need to understand.
Target – A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few.
Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database.
Conditions – Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema ‘Banking’.
Policy – A set of rules specified for the server objects or the properties of database.
A “FILLFACTOR” is one of the important arguments that can be used while creating an index.
According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.
Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt. (Read more here)
Fill-factor settings of 0 and 100 are equal! (Read more here)
ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. If we want sum on different levels without adding any new column, then we can do it easily using ROLLUP. We have to just add the WITH ROLLUP Clause in group by clause. (Read more here)
It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.
It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query. (Read more here)
When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size. (Read more here)
There are three types of wait types, namely,
Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it’s not yet available.
Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
External Waits. External waits occur when an SQL Server worker is waiting for an external event. (Read more here)
If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below.
1) Convert the Recovery Model to Simple Recovery
If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete.
2) Start Taking Transaction Log Backup
In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.
No, we can’t see definition of encrypted stored procedure in Activity Monitor.(Read More Here)
Reference: Pinal Dave (http://blog.SQLAuthority.com)