Restarting Microsoft SharePoint Foundation Web Application service is generally preferred through STSADM command instead of Central Admin

Today we had issues on one of the web nodes and hence we restarted Microsoft SharePoint Foundation Web Application using Central Admin. The service kept showing “Starting”.

We redid our IIS bindings expicilty.

The using STSADM command stsadm -o provisionservice -action start -servicetype spwebservice executed on DOS prompt on \Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN directory.

Issued iisreset command.

Now the service shows started through Central Admin.

MSDN Reference

SSRS Report is taking forever – OPTION (OPTIMIZE FOR UNKNOWN) hint – Parameter sniffing

Today one of the SSRS reports kept on loading.
On analyzing the stored procedure associated with it, we decided to use OPTION (OPTIMIZE FOR UNKNOWN) hint.
Why?
This stored procedure was creating 5 temp tables. We checked and found out that all these 5 temp table creation was instantaneous.
It was using the 5 temp tables and 8 other tables and performing the query, which was taking forever.
So we added the OPTION (OPTIMIZE FOR UNKNOWN) hint in this query and Viola! the query took less than 1 second.

So what is this parameter sniffing?

The Brent OzarTurgay Sahtiyan and Gregory Larsen have explained nicely what is parameter sniffing.

SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache ( or procedure cache). After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it, regardless different parameters are passed.

The potential problem with this approach is the parameters that were used when the plan was cached might not produce an optimal plan for all execution of the SP, especially those that have significantly different set of records returned depending on the parameters passed. For instance, if you passed parameters that required a large number of records to be read, the plan might decide a table or index scan would be the most efficient method to process the SP. Then if the same SP was called with a different set of parameters that would only return a specific record, it would used the cached execution plan and perform an table or index scan operation to resolve it’s query, even if a index seek operation would be more efficient in returning the results for the second execution of the SP.

Read here to know more about scans vs seeks.

References

Reference 1

Reference 2

MSDN Execution Plan Caching and Reuse –  SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache.

SQL Server execution plans have the following main components:

  • Query PlanThe bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

 

  • Execution ContextEach user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

 

How to implement SQL Server Job dependencies

Various ways to implement SQL Server job dependencies:

  1. We need to start job B after job A finishes. In the last step of job A add a step to start job B using SQL code – EXEC msdb..sp_start_job @job_name = ‘Job B’.
  2. We need to start job B after job A finishes successfully, but if Job A fails then execute some SQL statement, send email notification and start job C. In above scenario we can have following SQL code –

Declare @Status char(4)
set @Status = ‘N’
declare @body1 nvarchar(MAX)
IF Exists (select distinct run_status from msdb.dbo.sysjobhistory
where job_id = (select job_id from msdb.dbo.sysjobs where name = ‘Job A’)
and run_status = 1
and run_date = cast(convert(char(8),getdate(),112) as int)

)set @Status = ‘Y’
else set @Status = ‘N’
WAITFOR DELAY ’00:00:05′
if @Status = ‘Y’
Begin

EXEC msdb..sp_start_job @job_name = ‘Job B’
End

Else
Begin
–set of SQL statements for recovery action
set @body1 = ‘Server :’+@@servername + ‘ Job A failed and hence job C Recovery started’
EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com; abc1@xyz.com’,
@profile_name = ‘EMAIL PROFILE NAME’,
@subject = ‘Job A failed and hence job C Recovery started’,
@body = @body1,
@body_format = ‘HTML’ ;

EXEC msdb..sp_start_job @job_name = ‘Job C’

End

3. We need to start job B, running every hour till 5 pm, after job A finishes successfully, sometime in the early morning. Create scheduler for job B to run every hour starting from 9 am to 5 pm.

Now the first step in job A should be to disable the job B scheduler:

Use msdb

go

EXEC dbo.sp_update_schedule     @name = ‘SchldPSIncrementalLoadRefreshEvery1Hour’,     @enabled = 0

Now the last step in job A should be to enable the job B scheduler:

Use msdb

go

EXEC dbo.sp_update_schedule     @name = ‘SchldPSIncrementalLoadRefreshEvery1Hour’,     @enabled = 1