Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Range Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 8:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 66, Visits: 285
I have this query below that displays the size of the compressed backups for today's date. I need to change it but all my attempts broke the query. I would like to go back 3 days from whenever I run this and have the total sum of compressed backups from these three days displayed.


select distinct @@servername, convert(varchar,a.backup_start_date,101) 'Date', a.database_name 'DB Name',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id

where convert(varchar,a.backup_start_date,101) = convert(varchar,getdate(),101)
and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.backup_set_id, a.database_name, a.backup_start_date
order by 1
Post #1436526
Posted Thursday, March 28, 2013 8:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 1,726, Visits: 487
Is this what your looking for:

select distinct @@servername, a.database_name 'DB Name', 
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id

where convert(varchar,a.backup_start_date,101) >= convert(varchar,getdate()-3,101)
and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.database_name
order by 1

Post #1436539
Posted Thursday, March 28, 2013 12:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 66, Visits: 285
Yep, Thanks!
Post #1436668
Posted Saturday, January 11, 2014 9:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 155, Visits: 76
Hi folks,

The script is fine but as per performance concern we should not use any function in where condition. My script is

Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)

data retrieving from trigger table for last 3 days to get the last updated/inserted/deleted from our production table but script is running very slow as well i am getting wrong data also as my date format is in datetimestamp format in trigger table

can any body please guide me how can i overcome this issue.


Thanks
Post #1530075
Posted Sunday, January 12, 2014 3:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 459, Visits: 257
Hi, two things (in where clause)

1.
select name from sys.databases where database_id not in(1,2,3,4)
should be
select name from sys.databases where database_id >4

2.
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
should be


Kindest Regards,

Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
Post #1530085
Posted Sunday, January 12, 2014 9:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
Damian Widera-396333 (1/12/2014)
Hi, two things (in where clause)

1.
select name from sys.databases where database_id not in(1,2,3,4)
should be
select name from sys.databases where database_id >4


To add to that, they could automate exclusion of certain user databases, as well. For example, they probably don't want to do this process on Read/Only or Offline databases so they could check the "is_read_only" and the "state" (0 = Online) flags in sys.databases. If a database is a scratchpad database (similar to what TempDB is), it will likely be in the SIMPLE recovery mode so they could also check the "recovery_model" for <3 (3 is SIMPLE recovery). Yeah...agree... that's IF they're doing proper backups on the other 2 models and they don't have the SIMPLE recovery mode on any databases that they're actually backing up.



2.
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
should be


BWAA-HAAA! Considering the time of morning that you posted that, you must've done like I sometimes do... nod off right in the middle of a post.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530115
Posted Sunday, January 12, 2014 10:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
suryam (1/11/2014)
Hi folks,

The script is fine but as per performance concern we should not use any function in where condition. My script is

Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)

data retrieving from trigger table for last 3 days to get the last updated/inserted/deleted from our production table but script is running very slow as well i am getting wrong data also as my date format is in datetimestamp format in trigger table

can any body please guide me how can i overcome this issue.


Yes and agreed. Wrapping columnns in a function usually results in a SCAN instead of a SEEK/Range scan and that can really hurt performance.

To convert your code to be able to use indexes on the id_updt (Seriously??? They abbreviated that??? ) you need to absolutely avoid any calculations on table/view columns. The following is an exact but SARGable replacement for the code you've given.

  WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-3,0) --Midnight 3 days ago not including today


"SARGable" effective means "can do an index seek" and the "0" in the code above is the date-serial number (shortcut) for '1900-01-01'. The code calculates the number of whole days since the "0" date and then converts that number of whole days back to a date effectively stripping the time off the date making it a WHOLE date.

The problem with your code and the replacement code above is that it's highly dependent on when you run it because there is no end date to it. What you might want to do is covert it to return the last 3 WHOLE days and that doesn't include today because today isn't ever done yet. I don't know what other people call it but I call it "boxing the dates".

The following code will return the rows for the last 3 whole days and will produce the same results no matter what time of day you run it (provided that id_updt rows are static).

  WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-4,0) --Midnight 4 days ago not including today (4 necessary because TODAY is not included)
AND id_updt < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) --Midnight at the start of today


Let us know if you have any addition questions on this.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530117
Posted Sunday, January 12, 2014 10:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
Terry300577 (3/28/2013)
Is this what your looking for:

select distinct @@servername, a.database_name 'DB Name', 
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id

where convert(varchar,a.backup_start_date,101) >= convert(varchar,getdate()-3,101)and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.database_name
order by 1



I know this post is a couple of months previous, but for the code that I've emphasized above, please consider "boxing the dates" rather than making a non-Sargable query out of it. Please see my post above (http://www.sqlservercentral.com/Forums/FindPost1530117.aspx).

Also, get out of the habit of using ORDER BY 1. It was never a good habit because the first column of the SELECT could certainly change and the method has actually been deprecated by Microsoft and will be removed from a future version of SQL Server.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530119
Posted Tuesday, January 14, 2014 12:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 155, Visits: 76
Thanks Jeff ,

i agree with ur suggestion to create index on id_updt column.

the script is same, which i have modified in my script given below

where id_updt>=convert(datetime,convert(varchar(10),getdate()-3,101))
and id_updt< getdate()

your script

WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-4,0)
AND id_updt < select DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

and the output is same with correct value , execution plan is same for both script doing table scan which i can avoid by creating a index.



Thanks
Post #1530562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse