SQL Server - Find User Defined Table Type Dependency

BEGIN TRANSACTION
DROP PROC dbo.uspGetStockAccumulatePerMarketTriggerByEventIDList
DROP PROC dbo.uspGetStockAccumulateTotalPortfolioByEventIdList
GO

---- WRITE HERE SCRIPT TO DROP OLD USER DEFINED TABLE TYPE AND CREATE A NEW ONE ----
/********************* Change History ********************
Date   Author Description
2016-08-02 Janice Get Stock Accumulate Per Market Trigger By EventId List

EXEC [dbo].[uspGetStockAccumulatePerMarketTriggerByEventIDList]
**********************************************************/
CREATE PROCEDURE [dbo].[uspGetStockAccumulatePerMarketTriggerByEventIDList]
@CompanyID INT,
@Table udttEventIDList READONLY
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CalculateStockAccumulateTypeId BIGINT
SELECT @CalculateStockAccumulateTypeId = [SettingParamValue] FROM [Setting](NOLOCK) WHERE [SettingParamName] = 'CalculateStockAccumulateType'

SELECT EventID
, WagerSelectionID
, IsRB
, ScoreHome
, ScoreAway
, MarketTypeID
, Handicap
, BetTypeSelectionID
,
--1:MixtureAmount; 2:PotentialPayout; 3: StakeAmount; 4: PotentialMemberExposure
CASE WHEN @CalculateStockAccumulateTypeId = 1 THEN MixtureAmount
WHEN @CalculateStockAccumulateTypeId = 2 THEN PotentialPayoutAmount
WHEN @CalculateStockAccumulateTypeId = 3 THEN StakeAmount
ELSE PotentialExposureAmount
END AS Stock
, WagerCount
FROM [StockAccumulatePerMarketTriggerBySelection] WITH (NOLOCK)
WHERE EventID IN (SELECT TT.EventID FROM @Table AS TT) AND (@CompanyID = 0 OR CompanyID = @CompanyID)

SET NOCOUNT OFF;
END

GO
/********************* Change History ******************************
Date   Author  Description
2016-03-30    Jason      Get StockAccumulateSelectionTotalPortfolio by event id list
********************************************************************/

CREATE PROCEDURE [dbo].[uspGetStockAccumulateTotalPortfolioByEventIdList]
@EventIdList udttEventIDList READONLY
AS
BEGIN
SET NOCOUNT ON;

SELECT
  [BusinessUnitID]
      ,[EventID]
      ,[MarketTypeID]
      ,[SportID]
      ,[MarketID]
      ,[PeriodID]
      ,[BetTypeID]
      ,[Handicap]
      ,[BetTypeSelectionID]
      ,[ScoreHome]
      ,[ScoreAway]
      ,[StockRawStartID]
      ,[StockRawEndID]
      ,[StakeAmount]
      ,[Stock]
      ,[CompanyPotentialWinAmount] AS 'PotentialPayoutAmount'
      ,[CompanyPotentialExposureAmount] AS 'PotentialExposureAmount'
 FROM [dbo].[StockAccumulateTotalPortfolioBySelection] (NOLOCK) WHERE EventID IN (SELECT EventID FROM @EventIdList) AND BetTypeID in (1,2,3,5)

END
GO
COMMIT

Index column order affect Index seek or scan

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|---|---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|---|---|   |

See how restricting on A first, as your first column eliminates more results than restricting on your second column first? It's easier if you picture how the index must be traversed across, column 1, then column 2, etc...you see that lopping off most of the results in the fist pass makes the 2nd step that much faster.

Another case, if you queried on column 3, the optimizer wouldn't even use the index, because it's not helpful at all in narrowing down the result sets. Anytime you're in a query, narrowing down the number of results to deal with before the next step means better performance.

Since the index is also stored this way, there's no backtracking across the index to find the first column when you're querying on it.

In short: No, it's not for show, there are real performance benefits.

Sql Server ConnectionTimeout vs CommandTimeout

ConnectionTimeout a property of Connection class in ADO.NET, is the time you would wait, for connecting to a given database, before flagging a connection failure. Default value is 30 seconds.

new SqlConnection().ConnectionTimeout = 10;

CommandTimeout a property of the Command class in ADO.NET, is the time you would wait, for a command (query, stored procedure, etc.) to return result set, before flagging an execution failure. Default value for CommandTimeout too is 30 seconds.

new SqlConnection().CreateCommand().CommandTimeout = 10;

Unlike ConnectionTimeout which are part of connection string, command timeouts are defined separately (hardcoded or as appSettings). Setting both of them to 0 (zero) would result in indefinite wait period which is generally considered a bad practice. You ideally want to set both of them to in accordance to your performance SLAs. Though at times, while running data heavy background jobs / workflows you may want to set your CommandTimeout to a larger value.

Reference Link

Why is this an Index Scan and not a Index Seek?

It is using an Index Scan primarily because it is also using a Merge Join. The Merge Join operator requires two input streams that are both sorted in an order that is compatible with the Join conditions.

And it is using the Merge Join operator to realize your INNER JOIN because it believes that that will be faster than the more typical Nested Loop Join operator. And it is probably right (it usually is), by using the two indexes it has chosen, it has input streams that are both pre-sorted according your join condition (LocationID). When the input streams are pre-sorted lie this, then Merge Joins are almost always faster than the other two (Loop and Hash Joins).

The downside is what you have noticed: it appears to be scanning the whole index in, so how can that be faster if it is reading so many records that may never be used? The answer is that Scans (because of their sequential nature) can read anywhere from 10 to 100 times as many records/second as seeks.

Now Seeks usually win because they are selective: they only get the rows that you ask for, whereas Scans are non-selective: they must return every row in the range. But because Scans have a muchhigher read rate, they can frequently beat Seeks as long as the ratio of Discarded Rows to Matching Rows is lower than the ratio of Scan rows/sec VS. Seek rows/sec.

Questions?

OK, I have been asked to explain the last sentence more:

A "Discarded Row" is one that the the Scan reads (because it has to read everything in the index), but that will be rejected by the Merge Join operator, because it does not have a match on the other side, possibly because the WHERE clause condition has already excluded it.

"Matching Rows" are the ones that it read that are actually matched to something in the Merge Join. These are the same rows that would have been read by a Seek if the Scan were replaced by a Seek.

You can figure out what there are by looking at the statistics in the Query Plan. See that huge fat arrow to the right of the Index Scan? That represents how many rows the optimizer thinks that it will read with the Scan. The statistics box of the Index Scan that you posted shows the Actual Rows returned is about 5.4M (5,394,402). This is equal to:

TotalScanRows = (MatchingRows + RejectedRows)

(In my terms, anyway). To get the Matching Rows, look at the "Actual Rows" reported by the Merge Join operator (you may have to take off the TOP 100 to get this accurately). Once you know this, you can get the Discarded rows by:

RejectedRows = (TotalScanRows - MatchingRows)

And now you can calculate the ratio.

Above Resourse Link

Below List Article Reference

Merge join Vs Hash join Vs Nested loop join

SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance

Sample script :

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO


Ref Link

Script All the Stored Procedures in The Database

Script All the Stored Procedures in The Database

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'V'

To script All the Functions in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'FN'

For scripting all Triggers small modification is required, instead of sys.objects I joined thesys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.triggers as O ON M.object_id = O.object_id

Resource Reference Link

Scripting dependency of User Defined Table Types

-- Find all referencing objects to user-defined table type in @fullObjectName parameter
-- and generate DROP scripts and CREATE scripts for them
CREATE PROC ap_FindReferences (@fullObjectName VARCHAR(200))
AS
BEGIN
    SET NOCOUNT ON

    IF (TYPE_ID (@fullObjectName) IS NULL)
    BEGIN
        RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName)
        RETURN
    END;

    WITH sources
    AS
    (
        SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects o ON o.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@fullObjectName)
    )

    SELECT 

        'DROP ' +
            CASE OBJECTPROPERTY(referencing_id, 'IsProcedure')
            WHEN 1 THEN 'PROC '
            ELSE
                CASE
                    WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION '
                    ELSE ''
                END
            END
        + SCHEMA_NAME(o.schema_id) + '.' +
        + OBJECT_NAME(m.object_id)    

    FROM sys.sql_expression_dependencies d
    JOIN sys.sql_modules m ON m.object_id = d.referencing_id
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE referenced_id = TYPE_ID(@fullObjectName)
    UNION  ALL
    SELECT  'GO'
    UNION  ALL
    SELECT
        CASE
            WHEN number = RowId    THEN DEFINITION
            ELSE 'GO'
        END
     FROM sources s
    JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1

END
GO

-- Invokes ap_FindReferences procedure and writes scripted result to .sql file 
CREATE PROC ap_WriteReferences
@typeToFind VARCHAR(200)
AS
BEGIN

    DECLARE @sqlCmd VARCHAR(500)
    DECLARE @database VARCHAR(200) = 'test'
    DECLARE @outputFile VARCHAR(500) = 'c:\refences.sql'

    SET @sqlCmd = 'sqlcmd.exe -d '+@database+' -q "EXEC ap_FindReferences '''+ @typeToFind +'''" -o '+ @outputFile +' -h-1 -y0'

    EXEC xp_cmdshell @sqlCmd

END