Spotlight on SQL Server Forum - Toad for SQL Server - Toad World
Gita Sharifi
Follow / 13 Jan 2017 at 5:34pm / Toad for SQL Server

What’s new for Spotlight on SQL Server Enterprise – January 2017

Expand content
This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledge Base Articles...

This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledge Base Articles, Product Life Cycle and Training Videos.

New enhancements and fixes of Spotlight 11.7.1 patch release.
https://support.quest.com/spotlight-on-sql-server-enterprise/kb/223943/?cmpid=soc:spotlight-line:communties:01



42 0 / Create an account to join the discussion.
Gita Sharifi
Follow / 15 Nov 2016 at 9:53pm / Toad for SQL Server

What’s new for Spotlight on SQL Server Enterprise – November 2016

Expand content
This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledge Base Articles...

This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledge Base Articles, Product Life Cycle and Training Videos.

New Knowledge Base Articles

https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/214812


New enhancements and fixes of Spotlight 11.7 patch release.

231 0 / Create an account to join the discussion.
Danny Torres
Follow / 1 Nov 2016 at 1:58pm / Toad for SQL Server

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
Do you have a quick support question and short of time? Contact us Via Twitter @ QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

Do you have a quick support question and short of time? Contact us Via Twitter  @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

334 0 / Create an account to join the discussion.
Gita Sharifi
Follow / 1 Nov 2016 at 12:57pm / Toad for SQL Server

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
Do you have a quick support question and short of time ? Contact us via Twitter @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

Do you have a quick support question and short of time ? Contact us via Twitter @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

 

311 0 / Create an account to join the discussion.
Gita Sharifi
Follow / 9 Sep 2016 at 2:47pm / Toad for SQL Server

Spotlight on SQL Server Enterprise - How to get the most out of this...

Expand content
The better a problem is described, the better the assistance tends to be. In order to better assist with your issue at hand, please include the following information in your initial post: - Product...

The better a problem is described, the better the assistance tends to be.

In order to better assist with your issue at hand, please include the following information in your initial post:

- Product version and relevant environment details such as server and database versions and builds.
- Include a screenshot of the problem or error.
- Describe the problem fully. What result was expected?
- Can the issue be created on demand or is it intermittent? Provide exact steps to recreate the problem if possible.
- Generate a support bundle file if necessary. In Spotlight, select Help | Support Bundle option to generate support bundle zip file.

*Note: Please refrain from including private data in your posts

Be as thorough as you can with the provided info. We will reply to you as quick as possible. We are glad to assist you.

788 0 / Create an account to join the discussion.
Gita Sharifi
Follow / 9 Sep 2016 at 2:33pm / Toad for SQL Server

Latest Release Information

Expand content
Here you can find information on our latest release, from features to downloads & documentation. - New features from our Release Notes documentation: https://support.software.dell.com/spotlight-on...

Here you can find information on our latest release, from features to downloads & documentation.

- New features from our Release Notes documentation:
https://support.software.dell.com/spotlight-on-sql-server-enterprise/release-notes-guides

 - Link to download the latest version of Spotlight:
https://support.software.dell.com/spotlight-on-sql-server-enterprise/download-new-releases

 - Link to Trial edition of Spotlight:
https://software.dell.com/products/spotlight-on-sql-server-enterprise/

1106 0 / Create an account to join the discussion.
Gita Sharifi
Follow / 8 Sep 2016 at 8:54pm / Toad for SQL Server

Top 5 Knowledge Base Articles

Expand content
Here you can find what knowledge articles are trending. These are this month’s Top Knowledge Base Articles: 1. What is new in Spotlight on SQL Server Enterprise 11.6.1? https://support.software...

Here you can find what knowledge articles are trending. These are this month’s Top Knowledge Base Articles:

1.  What is new in Spotlight on SQL Server Enterprise 11.6.1?
https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/198539


2. VIDEO - How to Install Spotlight on SQL Server Enterprise.
https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/177236


3. What is Spotlight federation?
https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/199397


4. Error during connect: "Error 800706BA: The RPC server is unavailable"
https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/56100


5. Error: "Access is denied (Exception from HRESULT: 0x8007005 (E_ACCESSDENIED)"
https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/84287

 
Feel free to browse them. Remember to sign in to get the entire information available for our Register users.

835 0 / Create an account to join the discussion.
brumly
Follow / 13 Jan 2017 at 11:56am / Toad for SQL Server
Latest post by on 13 Jan 2017 at 9:12pm

Table not Updating in Wait Statistics View

Expand content
Hi I do the following: Enter SQL Spotlight Press the Wait Statistics button Adjust the "playback" button to the time where i had an issue I see the graph at the top and the table...

Hi

I do the following:

  1. Enter SQL Spotlight
  2. Press the Wait Statistics button
  3. Adjust the "playback" button to the time where i had an issue
  4. I see the graph at the top and the table with the statistics below.
  5. I make a note of the Wait Time Total (ms) values for the top 3 wait types
  6. On the graph i click "show data for last" -> and change the time point to 1hr or  12 hours or 1 week
  7. The graph updates with new results ok (although sometimes it doesnt)
  8. In the table, the Wait Time Total (ms) value doesnt change.

Some things i tried for troubleshooting:

  1. I tried waiting up to 5mins but no change
  2. No errors in the event viewer
  3. Memory and CPU stay well below limits for the server
  4. The waitstats_{}.result files in <install_dir>\Agent\data\memory\<db_name> look to have been updated in the last minute.. though i dont know if this has anything to do with playback data..

Questions:

  • Is this a bug?
  • If not how do i get the table to show me the statistics for what appears in the graph (i.e. the way everything else functions in spotlight)

I worry about how much i can trust these wait statistics totals...

Brumly

53 1 / Create an account to join the discussion.
brumly
Follow / 13 Jan 2017 at 10:59am / Toad for SQL Server
Latest post by on 13 Jan 2017 at 3:52pm

system.outofmemoryexception

Expand content
Hi When moving about within the Wait Events section i get: "Error retrieving data: ClientConnectIssue: Exception of type 'System.OutOfMemoryException' was thrown" How should i...

Hi

When moving about within the Wait Events section i get:

"Error retrieving data: ClientConnectIssue: Exception of type 'System.OutOfMemoryException' was thrown"

How should i start troubleshooting this issue?

Is this a problem with the spotlight server or with the SQL server that the spotlight DB sits on?

Are there any default settings for memory on the spotlight server, or for the instance that spotlight DB is running on that i should be aware of?

Cheers

Brumly

61 1 / Create an account to join the discussion.
Ken.Hemmerling
Follow / 17 Dec 2016 at 5:37pm / Toad for SQL Server
Latest post by on 10 Jan 2017 at 4:18pm

How to force Spotlight to update Index Fragmentation report

Expand content
Hello everyone, My Spotlight on SQL Server reports an index with 99% fragmentation so I used the generated script to rebuild it. That was half an hour ago and Spotlight still shows the same index with...

Hello everyone,

My Spotlight on SQL Server reports an index with 99% fragmentation so I used the generated script to rebuild it.  That was half an hour ago and Spotlight still shows the same index with 99% fragmentation.  Is there a way to force Spotlight on SQL Server to update its Index Fragmentation data?

Ken

Answered 158 4 / Create an account to join the discussion.
Gita Sharifi
Follow / 26 Oct 2016 at 7:12pm / Toad for SQL Server

What’s new for Spotlight on SQL Server Enterprise – October 2016

Expand content
This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledgebase Articles...

This monthly publication will provide new and updated information regarding the products that we offer and organized in the following categories: Documentation, Product Notifications, Knowledgebase Articles, Product Life Cycle and Training Videos.

 

New Knowledgebase Articles

https://support.software.dell.com/spotlight-on-sql-server-enterprise/kb/212688

New enhancements and fixes of Spotlight 11.6.2 patch release.

389 0 / Create an account to join the discussion.
brumly
Follow / 25 Oct 2016 at 3:42pm / Toad for SQL Server
Latest post by on 26 Oct 2016 at 4:09pm

sys.dm_exec_connections in historic info in Spotlight

Expand content
Hi I'm specifically looking for whether num_writes is recorded as part of the sys.dm_exec_connections DMV in spotlight historic data, i.e. that i can go back to one week ago and see which query had...

Hi

I'm specifically looking for whether num_writes is recorded as part of the sys.dm_exec_connections DMV in spotlight historic data, i.e. that i can go back to one week ago and see which query had the highest num_writes?

I've looked through this forum but there are really not many posts. Neither on the blog. Is this the best resource for SQL spotlight?

The wiki is empty and the documentation on the Dell site is really thin... so struggling to find any information beyond a "getting started guide"

thanks

548 4 / Create an account to join the discussion.
Ken.Hemmerling
Follow / 19 Oct 2016 at 6:12pm / Toad for SQL Server
Latest post by on 21 Oct 2016 at 6:43pm

Collection 'SQL Cluster failover detection' failed : Execution...

Expand content
I have the following within Spotlight on SQL Server: Alarm: Monitored Server - SQL Server Collection Execution Failure Key: SQL Cluster failover detection Severity: Informational Raised: <today>...

I have the following within Spotlight on SQL Server:

Alarm:  Monitored Server - SQL Server Collection Execution Failure
Key:  SQL Cluster failover detection
Severity:  Informational
Raised:  <today>
Message:  Collection 'SQL Cluster failover detection' failed : Execution state was not set

The remainder of the message are the steps to disable this monitor.  Can anyone explain how to fix the root cause of this rather than just disabling the monitor within Spotlight?

Ken

Unanswered 628 5 / Create an account to join the discussion.
paul.bankston
Follow / 17 Aug 2016 at 4:10pm / Toad for SQL Server
Latest post by on 18 Aug 2016 at 7:10pm

Checking for High Availability Primary Replica to use in Alarm Ac...

Expand content
I have a two node SQL Server 2012 High Availability Cluster. I need to check to see which node is the Primary replica so I can configure certain Alarm Actions to only fire if the node is the Primary Replica...

I have a two node SQL Server  2012 High Availability Cluster.  I need to check to see which node is the Primary replica so I can configure certain Alarm Actions to only fire if the node is the Primary Replica.  For example, log backups only occur on the current Primary Replica so I constantly get alarms from the Secondary that the databases' logs are not being backed up.

916 1 / Create an account to join the discussion.
colin.allen
Follow / 5 Jan 2016 at 1:59pm / Toad for SQL Server
Latest post by on 4 Aug 2016 at 8:34pm

Scripting Alarms or Alarm Actions

Expand content
I'd like to be able to script changes to alarms so that I can include the changes in deployment scripts, for example, we have two servers which flip between Production and Preproduction. When the server...

I'd like to be able to script changes to alarms so that I can include the changes in deployment scripts, for example, we have two servers which flip between Production and Preproduction. When the server is in the preproduction role we don't back up the databases, when it flips to production the backup jobs are enabled, so we only want backup alerts when the server is in the production role and we don't really want to have to manually reconfigure the alerts. Is there a way to script the changes to the exclusion list or flip the tag from production to preproduction? 

2074 1 / Create an account to join the discussion.
emil.bialobrzeski
Follow / 2 Aug 2016 at 10:28am / Toad for SQL Server
Latest post by on 4 Aug 2016 at 8:51am

Plan Pollution

Expand content
Hi, I have noticed that Spotlight is causing quite a plan pollution with his monitoring queries. SELECT query_hash , COUNT(DISTINCT ( query_hash )) AS DistinctPlanCount , COUNT(query_hash) AS PlanCount...

Hi,

I have noticed that Spotlight is causing quite a plan pollution with his monitoring queries.

SELECT query_hash ,
COUNT(DISTINCT ( query_hash )) AS DistinctPlanCount ,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
ORDER BY COUNT(query_hash) DESC;

If you look closely at some of the top ones these are either not parameterized queries or different white spaces.

SELECT q.PlanCount ,
q.DistinctPlanCount ,
st.text AS QueryText ,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash ,
COUNT(DISTINCT ( query_hash )) AS DistinctPlanCount ,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
AND qs.query_hash = 0x8FD48F3C03B151EB
ORDER BY q.PlanCount DESC;

974 2 / Create an account to join the discussion.
elizabetht
Follow / 12 Jul 2016 at 2:25am / Toad for SQL Server
Latest post by on 12 Jul 2016 at 10:56pm

Is it unwise to install spotlight on the same VM as the SQL instance...

Expand content
Hi everyone, First - apologies for what must be a noob question. I am looking to install a trial version of Spotlight to investigate DB application performance issues. We have one SQL instance (we...

Hi everyone,


First - apologies for what must be a noob question.


I am looking to install a trial version of Spotlight to investigate DB application performance issues.  We have one SQL instance (we are a small org) on a dedicated VM serving several production databases (all 3rd party).  Is it it a bad idea to install spotlight on that same VM?  Should I be looking to install it on a different VM?

Will be purchasing a full licence if it turns out to be as useful as it sounds.

(Yes I have researched this extensively, but "install" "separate", "architecture" etc are giving very broad/varied results.)

Thanks

Elizabeth

1148 3 / Create an account to join the discussion.
bkim_99845
Follow / 3 Jul 2016 at 10:40pm / Toad for SQL Server
Latest post by on 4 Jul 2016 at 3:35pm

san performance

Expand content
Hi, does anyone know how to query "max i/o wait" value from the backend database? Thanks, Ben

Hi, does anyone know how to query "max i/o wait" value from the backend database?

Thanks,

Ben

1191 1 / Create an account to join the discussion.
Howard
Follow / 24 May 2016 at 11:09pm / Toad for SQL Server
Latest post by on 27 May 2016 at 8:08pm

WorkloadAnalysis Extended Event

Expand content
The information collected by the WorkloadAnalysis extended event is really wonderful. Is there much overhead in capturing details of each query that is run on the SQL instance 24/7? Workload analysis is...

The information collected by the WorkloadAnalysis extended event is really wonderful.  Is there much overhead in capturing details of each query that is run on the SQL instance 24/7? Workload analysis is enabled by default so I'm hopeful that it is very lightweight.

Thanks,

Howard

1477 2 / Create an account to join the discussion.
FarmboyEsq
Follow / 14 Mar 2016 at 3:53pm / Toad for SQL Server
Latest post by on 18 Apr 2016 at 4:23pm

High Overhead Query References @Dell_EnumJobs and @Dell_JobRunDet...

Expand content
Hi, Spotlight Essentials reveals the SQL behind the worst performing queries in one of our instances. (See SQL below). The query is from a larger script that must allocate declared temporary tables named...

Hi,  Spotlight Essentials reveals the SQL behind the worst performing queries in one of our instances.  (See SQL below).  The query is from a larger script that must allocate declared temporary tables named @Dell_EnumJobs and @Dell_JobRunDetails.  

Can anyone provide sample SQL for allocating (and loading?) these table variables, so I can go about tuning the larger query?

Thanks.

Here is the full query:

select
j.name as JobName,
c.name as Category,
j.enabled as Enabled,
case ej.running
when 1 then N'Running'
else case h.run_status
when 2 then N'Inactive'
when 4 then N'Inactive'
else N'Completed'
end
end as CurrentStatus,
coalesce(ej.current_step,0) as CurrentStepNbr,
LastRunDateTimeX as LastRunTime,
dateadd(s, LastRunDurationSec, LastRunDateTimeX) as LastRunFinishTime,
case h.run_status
when 0 then N'Fail'
when 1 then N'Success'
when 2 then N'Retry'
when 3 then N'Cancel'
when 4 then N'In progress'
end as LastRunOutcome,
case h.run_status
when 0 then -- combine message from job outcome (step 0) and last step that ran
(select top 1 message from msdb.dbo.sysjobhistory with (nolock) where job_id = j.job_id and step_id = 0 order by instance_id desc) + N' - '
+ isnull((select top 1 message from msdb.dbo.sysjobhistory with (nolock) where job_id = j.job_id and step_id > 0 order by instance_id desc, step_id desc), N'Unknown')
else ''
end as LastRunMessage,
LastRunDurationSec as LastRunDuration,
case when ej.next_run_date > 20000000 then
convert(datetime,
substring(ej.next_run_date,1,4) + N'-'
+ substring(ej.next_run_date,5,2) + N'-'
+ substring(ej.next_run_date,7,2) + N' '
+ substring(ej.next_run_time,1,2) + N':'
+ substring(ej.next_run_time,3,2) + N':'
+ substring(ej.next_run_time,5,2) + N'.000'
, 121)
else null
end as NextRunTime,
j.description as Description,
case when datediff(day,(case when ej.last_run_date > 20000000 then
dateadd(ss,
datediff(ss,convert(datetime,0,112),
dateadd(hh,(cast(h.run_duration as bigint)/10000),
dateadd(mi,(cast(h.run_duration as bigint)/10000%100),
dateadd(ss,(cast(h.run_duration as bigint)%100),
convert(datetime,0,112))))),
LastRunDateTimeX)
else null end),CURRENT_TIMESTAMP) < 8
and h.run_status = 0
and ej.running <> 1
and j.enabled = 1 then 1
else 0

end as RaiseAlarm,
case
when not (c.name like N'REPL-%') and c.name <> N'Replication' then
case when ej.running = 1 then datediff(s, l.start_execution_date,getdate())
else null
end
else null
end as currentrunduration,
averagerunduration
from
@Dell_EnumJobs ej
left join @Dell_JobRunDetails l on ej.job_id = l.job_id
left join msdb.dbo.sysjobs j on ej.job_id = j.job_id
left outer join msdb.dbo.syscategories c on j.category_id = c.category_id
left outer join msdb.dbo.sysjobhistory h with (nolock) on ej.job_id = h.job_id
and ej.last_run_date = h.run_date
and ej.last_run_time= h.run_time
and h.step_id = 0
cross apply
(
select LastRunDateTimeX =
case when ej.last_run_date > 20000000 then
convert(datetime,
substring(ej.last_run_date,1,4) + N'-'
+ substring(ej.last_run_date,5,2) + N'-'
+ substring(ej.last_run_date,7,2) + N' '
+ substring(ej.last_run_time,1,2) + N':'
+ substring(ej.last_run_time,3,2) + N':'
+ substring(ej.last_run_time,5,2) + N'.000'
, 121)
else
null
end
) LRDT
cross apply
(
select LastRunDurationSec = case when cast(h.run_duration as bigint) > 0 then
(cast(h.run_duration as bigint)/1000000)*(3600*24)+ -- incase it goes to days!
(cast(h.run_duration as bigint)/10000%100)*3600+
(cast(h.run_duration as bigint)/100%100)*60+
(cast(h.run_duration as bigint)%100) end
) LRDS

1735 1 / Create an account to join the discussion.