A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Viewing SPIDs In order to view all the connections in SQL Server execute the following query. [cc lang=”sql”] SELECT * FROM sys.dm_exec_sessions [/cc] From here we see a session_id shown in the left hand column. This is also known as the SPID. To find the SPID for your current execution window run this. [cc lang=”sql”] SELECT @@SPID [/cc] Connection Settings Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above. Transaction Isolation SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this Temp […]
Continue reading ...
To list all databases in a SQL Server instance, run the following: [cc lang=”sql”] SELECT * FROM sys.sysdatabases [/cc] This also outputs metadata about each database including createdate, compatibility level, and the MDF filename.
Continue reading ...
There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way. [cc lang=”sql”] SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER BY TABLE_SCHEMA + ‘.’ + TABLE_NAME [/cc] This method makes use of documented INFORMATION_SCHEMA system view. The next method makes use of two not so well documented system views. [cc lang=”sql”] SELECT FullName = s.name + ‘.’ + t.name ,SchemaName = s.name ,TableName = t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id ORDER BY s.name, t.name [/cc] There is really no difference between these two methods. The third method uses the sp_tables stored procedure and passes in the parameter of ‘TABLE’. Though the parameter being passes looks funny, this is how to make it work. [cc lang=”sql”] EXEC sp_tables @table_type = “‘TABLE’” [/cc] Show all Tables with Rowcount and Dataspace This method is the most extensive. Not only will it show you all the tables, but it will also display the rowcount and datasize in Megabytes. [cc lang=”sql”] SELECT * FROM ( SELECT TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME ,[RowCount] = SUM(sp.[rows]) ,Megabytes = (8 * SUM(CASE WHEN sau.type != 1 THEN sau.used_pages WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024 FROM INFORMATION_SCHEMA.TABLES t JOIN sys.partitions sp ON […]
Continue reading ...
The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include: TCP Ports Named Pipes Shared Memory VIA (Virtual Interface Adapater) – Deprecated When communicating, SQL Server can actually use a combination of all three of these protocols for different sessions depending on whether or not they are enabled. You can find more specifics regarding these protocols here. If that doesn’t make things complicated enough, within the TCP protocol, static or dynamic ports can be defined. If the communication is set to a static port (most common) then it is by default 1433. If it is set to dynamic, then it typically uses a range of ports. It is this range of ports that is not easy to determine, unless you use a command at the command line and correlate the associated process ID with that of the SQL Server instance you are investigating. The easy way to find the ports is to use the following script which will enumerate all ports that all SQL Server instances are using. (Once you download the script, rename it so SQLListeningPorts.vbs). The script is also shown below. All you need to do is cut and paste it into notepad, save it with a .vbs extension and double click it. (or right click and ‘open with’ Microsoft Script Host). Show All SQL Ports VB Script [cc lang=”vb”] […]
Continue reading ...
sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server. [cc lang=”sql”] EXEC sp_configure [/cc] These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options this procedure does not show us unless we change an additional configuration setting, named, ‘show advanced options’. Let’s do that now. [cc lang=”sql”] EXEC sp_configure ‘show advanced options’, 1 — Changing to 1 turns it on GO RECONFIGURE WITH OVERRIDE [/cc] Now that we have changed this option, let’s execute sp_configure again and look at the result. [cc lang=”sql”] EXEC sp_configure [/cc] Now we should see upwards of 70 configuration options we are able to set on SQL Server. In order to set them, we only need to copy the exact name listed in the ‘name’ column, and pass it as the first parameter for sp_configure, then for the second parameter, pass our desired value. [cc lang=”sql”] EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE WITH OVERRIDE [/cc]
Continue reading ...
