This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is SQL Profiler?
Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
Use SQL Profiler to monitor only the events in which you are interested.
If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected.
Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.
What is Builtin/Administrator?
The Builtin/Administrator account is basically used during some setup to join some machine in the domain. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.
How can SQL Server instances be hidden?
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties.
After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode – SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.
How can we check whether the port number is connecting or not on a Server?
TELNET [HOSTNAME] PORTNUMBER
Can we uninstall/rollback a service packs from SQL Server?
No: To rollback a SP you have to uninstall entire product and reinstall it.
What do you know about the Hotfixes?
Hotfixs are software pathces that were applied to live i.e. still running systems. A hotfix is a single, cumulative package that includes one or more filfes that are used to address a problem in a software product.
Why shrinking of database is bad in SQL Server?
Shrinking a database increases fragmentation which causes performance problems.
I talked about how databases are like boxes being filled with content every day. As the box gets filled, the amount of free space decreases. But if you know that you will need to fill it up with more content, wouldn’t you go get a bigger box? Sadly, that’s not what we’re doing when we’re shrinking databases.
We’re basically throwing away the box with free space to replace it with a smaller box with just enough space to keep all of the existing content. Imagine what you would do to accomplish this task – empty the box of its contents, throw away the box, replace it with a smaller box and put the contents back in.
Can we install SQL Server using a configure file?
Yes: We can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section.
Which Dynamic memory view is used to check memory usage by SQL Server?
We can use this view sys.dm_os_process_memory.
What do you know about the Lock Page in Memory?
Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems.
What do you know about the SQL Server Latches?
- A latch is used to cordinate the physical execution of multiple threads within a database.
- A latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server. A latch is nothing more than a so-called Critical Section in multi-threaded programming.
- For row-level locking it was very important to introduce a concept like latching, because otherwise it would give rise to phenomena like Lost Updates in memory.
What are the different types of Lathces in SQL Server?
- IO Latches
- Buffer Latches (BUF)
- Non-Buffer Latches (Non-BUF)
Can DBA control or manage the SQL Server Latches?
No: It is not possible, It’s internally manage by the SQL Server. We can just check the different statuses of latches.
What is a User Database?
A user database is a database that we create to store data and start working with the data.
Why is all of the memory allocated for SQL Server not shown in Windows Task Manager?
The” Mem Usage” column shows the amount of memory for the working se t. The working set of a process is the set of pages in the virtual address space of the process that are currently resident in physical memory.
To find out how much memory SQL Server has allocated through the AWE mechanism, you can use the sys.dm_os_memory_clerks DMV.
Which autogrowth database setting is good?
Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).
