If my SP consists of a single SELECT statement, is there a difference between specifying OPTION (RECOMPILE) in the SELECT statement and defining the proc itself WITH RECOMPILE?
I mean, is one way substantially better/worse than the other?
If this statement is only run in the proc and it's recompiled every time, I guess there's no point in caching the plan. So in this case OPTION (RECOMPILE) is worse than WITH RECOMPILE in the proc, correct?
That's a really good point. Only problem is, I wouldn't know how to retrieve the plan in the first place, not mention what to do with it. But I certainly need to remember about this aspect.
then we have a problem https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query "RECOMPILE Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes,"
My SELECT statement is on a table that is very frequently updated. I'm guessing recompile will be triggered by the multiple updates anyway. I'll probably try first without recompile hints of any kind and see how it goes.
(And by updates I mean updates, inserts and deletes.)
@gbn The table is a kind of queue. Rows appear, get processed, then removed (data moved to another table). Plus each location will be working with its own subset of rows. At this point it's very unlikely that the table can have even more than 1,000 rows (total) at any given time.
So the principal filter will be on the Location attribute. Some other filters will be used as well, but they won't affect the number of rows significantly.
@sp_BlitzErik Depending on the application we already have the data synced up from Prod Via either Mirroring or Log Shipping. So for the most part we just break those. Apps connect, packages run, or reports are ran against the BCP instance once DB's are online. Then that's pretty much it. We re fire up log shipping and resync the mirrored DB's and good to go.
Because I can't figure out why the ()@#*$ my SRE team decided to rewrite the entirety of a known-working LTR release creation process. Because we're building a branch from January this year to provide an LTR to one of our special clients and ...
Since January I've rewritten our build system (delivered in like June) and it redid EVERYTHING. I reduced our intermediate build size from 70GB to 4GB (including source repo and git history and everything) and the build time (on my machine) from 40m - 8m.
So, because I've gone and spoiled everyone making life like 1000x better, on a process I've said for four years was wrong, but which I was told this isn't the time to pay down that technical debt (which was only )*(@#$) written 4 years ago, by a non-developer) for 4 years
SO because I've gone and spoiled the team, he just wanted the OLD output to match the NEW output
Instead of understanding the antiquated build system
I swear, I'm gonna either pop a blood vessel or strangle someone
No, what's really gonna happen is I'm going to reclaim the entirety of the deploy process and take that control out of everyone's hands and perfect an auto deploy to AWS and internal servers.
With live rolling no-downtime updates
Because I can do that, and a team of four people working non-stop for four months can't.
@jcolebrand and they haven't told anyone/you that they are going to do that? I would expect a 4-man 4-months project would be associated with a task/issue in a JIRA or similar system.
i can sympathize. a long time ago i re-wrote an ugly process that detached, zipped and copied mdf and ldf, and then deleted them as part of an archival to just take a backup and drop them. i heard it got changed back to the old one because people found it harder to run restore commands than to attach with the gui.
I don't need four months to automate machine setup, machine startup with deployment of files, testing the new IP address to make sure the site comes up, then rolling it into the LB, then repeating the process.
Updated timeline:
Done changes rolled out on meta.se and to Mods on all sites on Monday (9/18)
Assuming no significant issues are discovered we will roll out to all sites within the next couple weeks
I'm the new product manager for the DAG team. I’m excited to announce that the...
Stack Exchange releases "data dumps" of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Exchange Data Explorer (SEDE). Over time, as new features and other bits of data are introduced to Stack ...
Updated timeline:
Done changes rolled out on meta.se and to Mods on all sites on Monday (9/18)
Assuming no significant issues are discovered we will roll out to all sites within the next couple weeks
I'm the new product manager for the DAG team. I’m excited to announce that the...
