#Resolving SSMS Pauses

[thedbahub]

Diagnosing and Fixing SSMS Activity Monitor Pauses and RPC Errors

The “Paused” status in the Activity Monitor could be a symptom of a deeper issue within a SQL Server environment, possibly related to communication or service availability. If there is a question mark it likely indicates a connectivity or configuration issue that SSMS can’t resolve, hence its uncertainty about the server’s status. The “RPC Server is Unavailable” error from the outside monitoring…

[marcosplavsczyk]

When it comes to SQL Server performance, you might not think of it as a big deal because of today’s modern hardware but every once in a while, your office phone will start ringing off the hook, your email inbox will start blowing up… SQL Server has been brought to its knees. What happened?

That’s what we are going to look at in this article. The main goal is to identify what some of the common problems are and take a close look at the SSMS Performance Dashboard tool. This tool is available out of the box for DBAs allowing to diagnose and resolve their SQL Server performance issues quickly and easily for all supported versions of SQL Server from SQL Server 2008 to SQL Server 2017.

SSMS Performance Dashboard used to be a separate download that required a schema to be deployed but with the release of SSMS 17.2, Microsoft released the Performance Dashboard as a part of Standard Reports into SSMS eliminating the previous installation process.

So, let’s select a server in SSMS from Object Explorer, and from the right-click context menu choose Reports > Standard Reports and open the Performance Dashboard:

Next, we will see the data populated in the report:

This report is designed to help a DBA easily single out if there is any present performance killers on the targeted SQL Server. From the dashboard itself, we can further click on the hyperlinks to navigate to additional report for further drill through. For example, click the IO Statistics under the Historical Information to show the number of IOs performed for each database since it was brought online:

Each hyperlink will generate a report that puts the targeted task into a specific category, so it’s easier to read the generated data and what makes this a great SQL Server monitoring solution.

The most common performance issues which the Performance Dashboard can pick up and help you resolve it cover cases like:

CPU bottlenecks (shows queries with the most CPU utilization)

I/O bottlenecks (shows queries with the most disk activity)

Index recommendations (missing indexes)

Blocking

Resource contention (latch contention)

If a CPU holds back the rest of the system, SQL Server Performance Dashboard Report will capture additional diagnostic data that may be necessary to resolve the problem and point out the presence of the issue:

All this information can be used in a global set of SQL Server tools that make an ecosystem. For instance, after reading the reports, we can fire up the Activity Monitor from the same right-click context menu:

This monitor provides an overview of the active expensive queries among other useful information about SQL Server processes and how these processes affect the current instance of SQL Server:

Microsoft indicates that this monitoring has lightweight profiling infrastructure which basically means the service can run all the time and it only takes 1.5 – 2% of resource consumption in terms of CPU. This doesn’t hit overall performance of most SQL Servers and in return brings a lot to the table.

How, what we can do from here is right-click the actively expensive query and choose Show Execution Plan:

Why? Because recent CPU rate used by this query (see “sys.dm_exec_query_stats.total_worker_time“) is huge, and also the elapsed time of all completed execution of this query (see “sys.dm_exec_query_stats.total_elapsed_time“) is huge which means that this query could be running for hours.

This will open the Execution Plan for this query which we can examine further and look for bottlenecks. In this case, if we pause the mouse over the “Merge Join” operator which is the obvious performance killer with 93% of the total cost relative to the batch, we can view the description and properties of the operator in the displayed tooltip. As shown below, the Estimated number of Rows is extremely high (1,994,870,000) and that’s the cause of the long running query:

This is a very artificial example, but the point is that we found the smoking gun here. Let’s also take a look at a more real-world example and see what we can do when we come upon a slow/long running query.

Now, what I like to do before analyzing queries is to create a clear system state which basically means to remove as much as possible from the SQL Server cache. This is easily done by executing the query from below:

CHECKPOINT; GO DBCC DROPCLEANBUFFERS; DBCC FREESYSTEMCACHE('ALL'); GO

After this step, here’s another query captured as an expensive active query. When executed in SSMS, the status bar indicates that it took 3 seconds to complete:

This is all good, but when it comes to performance you can also consider another free tool like ApexSQL Plan that helps me analyze actual/estimated execution plans, and optimize SQL queries better than SSMS’s native execution plans.

Next, always remember to clear the cache and then execute the query if you want to capture more reliable results. Let’s run the same query in ApexSQL Plan and see the result. Create a new session, paste the code in the editor, along with the “clear cache” part in front, and from the Management tab hit the Actual button. If previously a connection was not established, set the targeted Server, Authentication method, Database and hit the OK to get the actual execution plan:

What we get inside the ApexSQL Plan is operator level performance information of an execution plan. Understanding all of the factors that are needed to ensure that a query is executed as quickly as possible is more of an advanced topic. I do want to highlight the fact that getting in, analyzing and truly optimizing queries does require a fair amount of experience with SQL Server. Also worth mentioning, is that some of the topics fall under the domain of your database developer or administrator. Now, with this little disclaimer being tossed out there it’s okay if you don’t understand the execution plan. But, on the other side, all we need to know right now is under the Select statement under the Duration column:

Note that the execution time is in milliseconds and that it’s 4,177 or 4 seconds. If you remember the result from the previous run in SSMS, it was 3 seconds. The reason why it took more for SQL Server to fetch the same data is that this time, the cache was cleared. Next, I’d like to mention another neat optimization tool part of the global ecosystem called Database Engine Tuning Advisor. Go back to SSMS, right-click anywhere inside the query editor and from the context menu choose Analyze Query in Database Engine Tuning Advisor command:

This tool examines how queries are processed in the databases you specify. All we need to do from here, is just to hit the Start analysis button at the top right:

Once the analysis is finished, under the Recommendations tab, you should see suggestions on how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning. In this case, there is only one index recommendation that should improve execution of this query at estimated 34%:

Let’s finish the optimization by going to Action > Apply Recommendations:

Hit the OK to confirm:

Once done, click Close to finish up the process:

Go back to ApexSQL Plan and execute the same query one more time. This time, the execution took 3,853 milliseconds:

This might not sound like a substantial improvement (4,177 to 3,853) but even a slight 8% improvement like this can go a long way if you think that this query could be coming from an application and the count could go as high as 1000 per hour. And remember, this 8% improvement is done with a single index creation.

I hope this article has been informative for you and I thank you for reading.

References: New in SSMS: Performance Dashboard built-in SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring !!! Start and Use the Database Engine Tuning Advisor

[kelsusit]

Hardware And Networking Interview Questions With Answers ~ Technical Help For Windows Server

Many folks in a selection of distinct countries utilizing e mail addresses offered by various servers have reported this dilemma. Primarily based on a sample of much more than 850 addresses recognized to be experiencing this difficulty, I have compiled a list of the 15 e-mail address sorts with the most situations. Table six beneath lists the domains in order of decreasing number of instances. The DS can’t derive a service principal name (SPN) with which to mutually authenticate the target server since the corresponding server object in the nearby DS database has no serverReference attribute.

INSTANCE PROFILE : Like start profile, Instance profile is certain to an instance. It means if there are 5 application servers every single will have one separate start profile with the settings certain to an instance. 5. If it is not functioning nonetheless, then you have to go to your Pc and get the DNS Server Address from there as pointed out ahead of , and set it manually on the Dashboard. 2. If the Protection Level is set to EncryptSensitiveWithUserKey, verify the Creator in your SSIS package and examine it with the SQL Server Agent Service account.

resource record is an entry in a name server’s database. There are a number of kinds of resource records utilized, which includes name-to-address resolution information. Resource records are maintained as ASCII files. •Oracle Grid Naming Service (GNS): Is a gateway in between the cluster mDNS and external DNS servers. The GNS method performs name resolution within the cluster. 4. ORDER BY in the dataset differs from the ORDER BY in the Tablixlist. You need to have to choose exactly where the data will be sorted. It can be completed inside SQL Server with an ORDER BY clause or in by the Reporting server engine. It is not valuable to do it in both. If an index is available use the ORDER BY in your dataset.

hi i use a three usb modem and connect an ethernet cable from my laptop to my xbox 360 to connect to xbox live. I have been utilizing this for about half a year but three have had an update and now it passes the network test on the dashboard but fails the web connection test. My IP address is right and my DNS server is the exact same as my IP address. What shall I do? please support. I have numerous e mail accounts hosted by 1and1 and I think their servers are in Germany and Pennsylvania. The oldest one particular is 17 years old and this is the e mail address name that is most typically being spoofed. Numerous of these accounts are utilised exclusively for forwarding Twitter messages from numerous Twitter accounts I preserve. I have been acquiring Spoof Spam Bombed in waves going back to September. They continue even although I have changed my password. Most current attack was yesterday.

This operation could not be completed because the DNS server listed as the present crucial master for this zone is down or misconfigured. Resolve the difficulty on the present important master for this zone or use yet another DNS server to seize the key master role. 1 or much more of the signing keys for a zone are not accessible to the DNS server. Zone signing will not be operational until this error is resolved. BOOTP sends a UDP message with a subnet work broadcast address and waits for a reply from a server that gives it the IP address. The identical message may possibly include the name of the machine that has the boot files on it. If the boot image place is not specified, the workstation sends an additional UDP message to query the server.

Each Serial Garbage collector and Parallel Garbage collector or Throughput GC are Cease the planet GC, it implies application thread stopped when Garbage collection takes place. Only difference in between Serial Garbage Collection and Parallel (Throughput GC) is that in Serial both Young Generation (Eden Space + survivor) or minor collection and complete GC (major collection) takes place serially which introduced larger pause time although in case of Parallel GC minor collection and significant collection takes place in parallel which final results in reduced or reduced pause time. An additional worth noting difference between parallel and serial GC is that former is more suited for Servers written in Java even though later is very best suited for Client application with lesser heap size.

Subscriptions are utilised to provide the reports to either File Share or E mail in response to Report Level or Server Level Schedule. Utilised to transfer one particular or a lot more SQL Server objects to a various database, either on the identical or yet another SQL Server instance. You can transfer tables, views, Indexes, stored procedures, User defined functions, Customers, Roles etc. There are currently no logon servers available to service the logon request. Bulk Insert Job is utilised to copy the large volume of data from text file to sql server destination.

Database is already started SAP instances are started 1 by one beginning with central instance. In default profile, parameters that are generally maintained for all situations are kept like enqueue server name, database server name, default client etc. But begin profile consists of what solutions need to run in a certain instance like message server, dispatcher and so on. As a result begin profile will be study first and then default profile. Hope I have answered your query. DNS uses UDP for communication between servers. It is a much better selection than TCP due to the fact of the improved speed a connectionless protocol delivers. Of course, transmission reliability suffers with UDP.

Compact deployment reduces the all round memory requirement of EPM Technique and also cuts down on the web applications startup time. Right after testing Workspace, Shared Services, HFM, Organizing, Monetary Reporting I discovered the JVM process consuming around 1.4 GB of memory which is nevertheless substantially significantly less than what the internet apps would call for when operating with the regular JVM-per-app configuration. The single managed server deployment appears like a beneficial way to minimize the memory specifications for small-scale EPM installations such as one particular-server Development environments.

The specified key service provider can’t be opened by the DNS server. The directory service was unable to transfer ownership of one or much more floating single-master operation roles to other servers. six. Keep away from dataset with result sets with a lot of records like more than 1000 records. A lot of times information is GROUPED in the report with out a Drill down choice. In that situation do the group by already in your dataset. This will save a lot of data transfer to the SQL Server and it will save the reporting server engine to group the outcome set.

2. EAL Information Sync Server can be installed on the OBIEE server into the very same EPM property with Essbase. The Analytics Hyperlink Server must be installed and configured on the EPM Foundation internet server (where WebLocig Admin Server is configured). And the HFM Connector part ought to go on the HFM internet server (which is usually the same as the Foundation net server). Note that the part solutions “IIS6 Metabase Compatibility” and “IIS6 Scripting Tools” need to be installed on the HFM web server just before you configure the HFM Connector component in EAL.

Cache Transformation: to cache the information used in the Lookup transform. When to use Full cache mode: When you happen to be accessing a big portion of your reference set When you have a small reference table When your database is remote or below heavy load, and you want to reduce the quantity of queries sent to the server When to use Partial cache mode: When you happen to be processing a small quantity of rows and it’s not worth the time to charge the complete cache. When you have a massive reference table. When your data flow is adding new rows to your reference table. When you want to limit the size of your reference table by modifying query with parameters from the information flow.

Junior and High College Students – Junior and Higher College students are a lot more responsible than younger students so you could want to think about buying new alternatively of used. New Macs come with a one particular-year warranty, and you can add on AppleCare to extend that out. They also ship with the newest hardware and software program, which signifies they will not become obsolete anytime soon. You will get numerous years of usage out of a new machine. It really is up to a parent to choose if the youngster is responsible with their individual belongings and deserves a new machine. If not, you can use our tips for the Elementary group to uncover a appropriate refurbished model.

DNS server not generating or reading the boot file for the directory service integrated DNS zone. five. Use the SQL Profiler to measure the efficiency of all datasets (Reads, CPU and Duration). Use the SQL Server Management Studio (SSMS) to analyze the execution plan of each dataset. 2.7. In the TargetServerURL text box, kind the URL of the target report server. Just before you publish a report, you must set this house to a valid report server URL. 1. Employing the Execute Package Utility () graphical interface a single can execute an SSIS package that is stored in a File Technique, SQL Server or an SSIS Package Retailer. DTEXECUI offers a graphical user interface that can be used to specify the different possibilities to be set when executing an SSIS package. You can launch DTEXECUI by double-clicking on an SSIS package file (.dtsx). You can also launch DTEXECUI from a Command Prompt then specify the package to execute.

The Deploy To Application Server process in EPM System Configurator now includes an selection to deploy all applications to a single managed WebLogic server. This so named compact deployment method existed as a manual choice in EPM 11.1.two.1 but the procedure has now been automated so that it only calls for a single box to be ticked on the internet application deployment panel. Single managed server signifies that web applications will be running below a single JVM and on Windows they are began using 1 Windows service.

Netstat is a little utility that numerous administrators use to monitor the network connections on their servers. It is really valuable for tracking down that small subset of functionality bottlenecks that aren’t attributable to yet one more piece of convoluted application code that some careless programmer wrote and now you have to take care of. But I digress. The element of RDL contains query or command and is employed by the Report Server to connect to the datasources of the report. 1. 1st the application at one endpoint-in this example, that would be the Net server-initiates what is called an “active close.” The Net server itself is now carried out with the connection, but the TCP implementation that supplied the socket it was utilizing nonetheless has some work to do. It sends a FIN to the other endpoint and goes into a state referred to as FIN_WAIT_1.

A number of connections to a server or shared resource by the very same user, making use of far more than one particular user name, are not permitted. Disconnect all previous connections to the server or shared resource and attempt once more. The directory service can not replicate with this server because the time because the final replication with this server has exceeded the tombstone lifetime. Every of these spam messages is sent to a list of recipients. The recipients are tightly correlated to the individual who owns the spoofed from address. I’ve observed examples of 4 to 54 recipients on a message. The quantity varies and is most likely purposely kept modest to decrease the possibility of becoming blocked by the getting e mail server if there are too several recipients since a message with a lot of recipients is a characteristic of spam messages.

HFM web and application server configuration tasks have not changed on the surface but the cluster details is now stored in Shared Services Registry rather than in the Windows registry. This indicates that all HFM app servers will choose up modifications completed to the cluster configuration with out possessing to repeat the modifications on all servers. For eg: keeps the catalog items in the Catalog table, the information supply data in the Information-Source table of ReportServer Database. The connections in the ESTABLISHED state are, nicely, established-they are neither getting set up nor torn down but just employed. This is what you will typically see the most of. But what about the other people? On a busy HTTP server, the number of sockets in this TIME_WAIT state can far exceed those in the ESTABLISHED state. For instance, I checked an IIS 6. box that serves a relatively busy corporate website earlier right now and got 124 ESTABLISHED connections versus 431 in TIME_WAIT.

These three functionality elements are logged each and every time for which a deployed report is executed. This data can be discovered in the table ExecutionLogStorage in the ReportServer database. The requested operation can be performed only on a worldwide catalog server. Delayed Create Failed Windows was unable to save all the data for the file %hs the data has been lost. This error was returned by the server on which the file exists. Please attempt to save this file elsewhere. Storage Region Networks Storage area networks (SANs) offer a high-speed infrastructure to move data amongst storage devices and file servers.

I realise that now the individual initiating these emails has the e-mail addresses in their possession there is small I can do to avoid them getting utilised. What I never recognize is how the e mail addresses had been harvested from my mailbox in the first location. I don’t consider that my user name and password had been compromised, despite the fact that that is a possibilty, so that leaves the servers that they are held on have been hacked or someone with acceptable access rights has deliberately passed on e-mail addresses to a 3rd party. I’m assuming information isn’t stored offshore to save on costs.

Filed under: Tagged: answers, hardware, inquiries, interview, networking, server, support, technical, windows from KelsusIT.com – Refurbished laptops, desktop computers , servers http://bit.ly/2zOzcw5 via IFTTT

[marcosplavsczyk]

SQL Server availability overview

The core component of SQL Server is the Database Engine service. It is identified with the Windows service named MSSQLSERVER (sqlservr.exe) for the default instance, and for the named instance, the customized instance name is appended to the name of the service, e.g. MSSQLTESTDEPT.

The database Engine service plays an important role in managing and handling relational databases and maintenance within SQL Server itself. Although there are other two optional services, SQL Server Agent and SQL Server Browser, the Database Engine service is sufficient for everyday work with SQL Server.

Inspecting status

There are several methods to determine and monitor the SQL Server service’s availability, and how long is it running, in order to monitor it continuously and provide stability of the SQL Server. This can be done manually or programmatically.

Services.msc

Open the Services.msc, and check the status of the MSSQLSERVER service (and other services, as well), like in picture below:

SQL Server Configuration Manager

Review the list of SQL Server-related services and check their status through the SQL Server Configuration Manager:

Checking availability with T-SQL

With stored procedure xp_servicecontrol

EXEC xp_servicecontrol N'querystate' ,N'MSSQLSERVER'

This stored procedure will show the current status of the service as a result:

Determining cause of changes

In monitoring, the availability of the SQL Server can be identified by online or offline status, simply by checking if the Database Engine service is running or not.

There are numerous reasons why this service can stop. It can be stopped by purpose or by some external event leading to the service shutting down.

The most common reason the SQL Server may have stopped is wrong logon information. It happens when the SQL Server domain account is changed, and logon information is not updated after that change. The service can be started, but without valid credentials, it can crash unexpectedly or stuck in “stopping” state. Solution for this is to timely update logon information in the properties of the specific Database Engine service (within Services.msc), whether the Local System account is chosen, or specific Windows user account:

Unlike the other SQL Server-related services, the Database Engine service can be paused, which will not cause an interruption of any active transactions to finish, but the service will not allow initializing of any new transactions on the particular SQL Server instance.

Changing the status

The Database Engine service can be paused, stopped or started again within SQL Server Management Studio, by right clicking on a desired instance in Object Explorer:

Note that MSSQLSERVER service cannot be initially started from SSMS. If the service is stopped, SSMS will not be able to connect on the particular instance and it will raise an error.

Other information

Information like temp object creation and login times can help us add context and information to SQL Server availability. The following can be useful information for tracking the availability of your SQL Servers

Determine the creation datetime of tempdb

Use this script on a desired SQL server instance to most accurately determine the actual login time of the Database Engine service, because system database, tempdb is recreated every time the Database Engine service initially starts (e.g., after system booting).

SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'

Collect detailed information with the sys.dm_server_services view

SELECT servicename AS ServiceName ,startup_type_desc AS StartupType ,status_desc AS ServiceStatus ,process_id AS ProcessID ,last_startup_time AS LastStartupTime ,service_account AS ServiceAccount FROM sys.dm_server_services WHERE servicename = 'SQL Server (MSSQLSERVER)'

This script will show the detailed information of the particular SQL Server, including startup type, the service status, process ID, last startup time and used service account (Windows User account):

Note that in WHERE statement, servicename should contain the full service’s name, like displayed.

Get list of SQL server-related processes with PowerShell

Use this PowerShell script to get all services which contains the string “SQL Server (“, and review their statuses (running or stopped):

Get-Service -computername <name_of_the_machine> | Where-Object{$_.DisplayName -like "SQL Server (*"}

The result should appear like this:

To get information from the remote machine, change the -computername variable into desired one.

Monitoring SQL Server availability with ApexSQL Monitor

Among the other special performance counters like SQL Server Agent status and System availability, ApexSQL Monitor incorporates the feature named Server availability, which monitors the SQL Server service status, display the information whether SQL Server is online or offline, and fires alert in moments when SQL Server passes from offline to online state, and vice versa.

Basic information

When All instances are chosen in left pane, the list of the instances will appear in ApexSQL Monitor Dashboard. If availability status of some instance changed in the meantime, the alert notification will be displayed under Status alert field:

Also, when select the particular instance, SQL Server overview will show additional information about SQL Server status (is it online or offline, and the login:

In this Dashboard view, information about current status of the service and its running time are present (marked above). In addition, there is the pie chart named Today’s server availability, and in this case, the ratio is 89% available/11% unavailable.

Availability alerts

Alerts related to the Server availability change from unavailable to available, and vice versa, can be reviewed and resolved in Alerts view, if needed. Also, resolved alerts can be documented with Reports feature (under Resolved alerts tab).

In this image above, the raised alert is on the left (marked), and the chart is on the right. As can be seen, there are two values on the scale, Online and Offline. Particular chart on the picture presents the moment when SQL Server becomes unavailable.

Availability metrics

The Server availability counter is the part of the SQL Server metrics group.

Alerts

To assure that SQL Server instance availability monitoring is going smooth, achieve minimum downtime of the service, and to automate the monitoring process, set the email alerting (for the email notification on raised alerts related to Server availability counter), and/or custom command alert actions in ApexSQL Monitor to restart or change configuration of monitored SQL server service.

Actionable alerts

Include this PowerShell command within the custom command alert action, in order to check status of the service, and if it is not running, the script will start it:

powershell.exe "if ((Get-Service -name MSSQLSERVER).Status -eq 'Stopped'){Start-Service MSSQLSERVER} if ((Get-Service -name MSSQLSERVER).Status -eq 'Running'){Restart-Service MSSQLSERVER}

Instead of MSSQLSERVER, other named SQL Server instance service name can be included. Also, if needed, the script can check and/or start the remote SQL Server instance:

powershell.exe "if ((Get-Service -name MSSQLSERVER -ComputerName <name_of_the_instance>).Status -eq 'Stopped'){Start-Service MSSQLSERVER} if ((Get-Service -name MSSQLSERVER -ComputerName <name_of_the_instance>).).Status -eq 'Running'){Restart-Service MSSQLSERVER}"

Check and change configuration of the particular SQL Server service (Startup type)

To make sure the SQL server service is running continuously , use this script within custom alert action to avoid the possibility of change:

powershell.exe “if ((Get-Service -name MSSQLSERVER).StartupType -eq 'Manual'){Set-Service MSSQLSERVER -StartupType Automatic} if ((Get-Service -name MSSQLSERVER).StartupType -eq Automatic){break}"

If the service’s startup type is any other than “Automatic”, the script will change its startup type. If it is already set to ”Automatic”, the script will terminate.

The post How to automatically monitor SQL Server availability appeared first on Solution center.