Thanks, this did the trick, It was date inserted or created column

SELECT TOP (1000)

col1, col2, ...

FROM tbl

ORDER BY <DateColumn> DESC

so, I looked at the table again and it has Date column, so will it work if I query based on the date column? if you have any sample query...

Thank you all for your suggestions, it helps!

I used IDENT_CURRENT(tables.name) to fetch the current max value.

SELECT

tables.name As TableName,

columns.name as ColumnName,

IDENT_CURRENT(tables.name)

FROM sys.tables tables

JOIN sys.columns columns

ON tables.object_id=columns.object_id

WHERE columns.is_identity=1

order by TableName

Thanks...I was looking for MAX value. I used the same query and looks like it gives current value(latest value)...I am find with current value as well.

Thank you All! for the information and help. I really do appreciate your time and admire the intention to help.

I will test out the suggestions and methods recommend. Learning every...

As a precaution, I always confirm before deletion and also script the SP's and save them on separate drive for couple of days so that I can recreate them if...

Great. Thanks!!

Now, at least I am ready with clear answers when I see blocking.

Obviously, fixing the root cause is deep. I work in an environment where there is lot of...

Thanks!!!, I ran the script for test scenario and below are the results..

BLOCKING_TREE

HEAD - 71 USE AdventureWorks2012 BEGIN TRANSACTION --rollback --commit SELECT * FROM [dbo].[DatabaseLog] WITH (TABLOCK, XLOCK, HOLDLOCK)

| |------...

Sure, I will have a look at the execution plans. Thank you!

Not a big performance improvement however replacing * where ever not needed. Since tb's have billions of records and '*' can or maybe a bottleneck...if anything the code tries to...

Here is the sql below:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'EXAMPLE',

@recipients = 'EXAMPLE@EXAMPLE.com',

@from_address = 'EXAMPLE@EXAMPLE.com',

@subject = 'TEST - EXAMPLE Scripts',

@body = 'EXAMPLE UPDATE'

Yeah....I missed that step..we need to build the .sln and then deploy using the wizard. Thanks!

Thanks, I guess the path is diff compared to what they mentioned for 2012

'\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd'

Great. Thanks!

I have noted the things you have mentioned and will now take a look at the SP's and look for additional problems as mentioned.