SQL Server – In memory table vs. temporary table – Benefits if temdb is already stored on RAM disk

In the documents we have an example of how temporary tables can be replaced by in-memory tables and a memory-optimized table:

  • is stored only in memory and has no component on the hard disk
  • does not contain any IO activity
  • does not include tempdb usage or competition

They said that:

The memory optimization leads to speed increases, which are often tenfold
faster or more.

I wonder if our system already uses RAM for the tempdb database. Which of the advantages will apply? I think they test a bit hard on a hard drive or SSD and doubt that they will get such good results.


More details. One of the most common table variable functions is the one that calculates access to a particular entity. Then the result is connected to different tables to obtain the query data. To optimize the latter links, I save the result of this function in a temporary table and the results are nice.

For very large volumes (for example, if the function returns 1-2 million entities), the execution of the function itself is slow (which is normal again because we put so many rows in the table variable).

So I'm thinking of rewriting this function as a stored procedure and putting the entities into the memory table in the hope that the CRUD operation will be done on the table 10 times faster or more,

SQL Server Replication – No new article could be added to the publication

I have the following replication script I did in the source database (the subscription is not added yet, the target database is not yet in the destination server):

use [myDatabase]
exec sp_replicationdboption @dbname = N & # 39; myDatabase & # 39 ;, @optname = N & # 39; publish & # 39 ;, @value = N & # 39; true & # 39;
GO

use [myDatabase]
exec sp_addpublication @publication = N & # 39; myDatabase_pub & # 39 ;,
@description = N & # 39; transactional publication of database & # 39; & # 39; myDatabase & # 39; & # 39; by Publisher & # 39; & # 39; DATABASE SERVER & # 39 ;. & # 39 ;,
@sync_method = N & quot; concurrent & # 39 ;, @retention = 0, @allow_push = N & nbsp; true & # 39 ;, @allow_pull = N & nbsp; true & # 39 ;,
@allow_anonymous = N? true ?, @enabled_for_internet = N? false ?, @snapshot_in_defaultfolder = N? true & # 39 ;,
@compress_snapshot = N = false, @ftp_port = 21, @ftp_login = N, anonymous, #allow_subscription_copy = N = false
@add_to_active_directory = N? false ?, @repl_freq = N? continuous ?, @status = N? active ?, @independent_agent = N? true & # 39 ;,
@immediate_sync = N? true ?, @allow_sync_tran = N? false ?, @autogen_sync_procs = N? false ?, @allow_queued_tran = N? false & # 39 ;,
@allow_dts = N? false ?, @replicate_ddl = 1, @allow_initialize_from_backup = N? false ?, @ enabled_for_p2p = N? false & # 39 ;,
@enabled_for_het_sub = N? false & # 39;
GO
exec sp_addpublication_snapshot @publication = N & # 39; myDatabase_pub & # 39 ;, @frequency_type = 1, @frequency_interval = 0,
@frequence_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0,
@frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0,
@publisher_login = N # username #, @Publisher_Password = N # password & # 39;
use [myDatabase]
exec sp_addarticle @publication = N # myDatabase_pub #, @article = N # simple_Table #, @source_owner = N # dbo #,
@source_object = N 'simple_Table', @type = N 'logbased', @description = null, @creation_script = null,
@pre_creation_cmd = N & # 39; drop @ #, @schema_option = 0x000000000803609F, @identityrangemanagementoption = N & # 39; manual & # 39 ;,
@destination_table = N 'simple_Table', @destination_owner = N 'dbo', @vertical_partition = N & # 39; false & # 39 ;,
@ins_cmd = N # sp_MSins_dbosimple_Table # @del_cmd = N # sp_MSdel_dbosimple_Table #
@upd_cmd = N # SCALL sp_MSupd_dbosimple_Table & # 39;
GO

use [myDatabase]
exec sp_addarticle @publication = N # myDatabase_pub #, @article = N # simple_Table_class #, @source_owner = N # dbo #,
@source_object = N_ simple_Table_class #, @type = N # logbased #, @description = null, @creation_script = null,
@pre_creation_cmd = N & # 39; drop @ #, @schema_option = 0x000000000803609F, @identityrangemanagementoption = N & # 39; manual & # 39 ;,
@destination_table = N = simple_Table_class #, @destination_owner = N # dbo #, @vertical_partition = N # false #
@ins_cmd = N & # 39; CALL sp_MSins_dbosimple_Table_class & # 39 ;,
@del_cmd = N & # 39; CALL sp_MSdel_dbosimple_Table_class & # 39 ;,
@upd_cmd = N # SCALL sp_MSupd_dbosimple_Table_class & # 39;
GO

This script worked well in a few months. If I let it run today, I got an error like:

The publication & # 39; myDatabase_pub & # 39; A new article could not be added because active schema modification activities exist or a snapshot is generated.

Do you know how to check what is blocking replication? What kind of scheme changes?

mysql – SQL Compare if two dates are the same

I make a small program in which I have to compare a date that I have stored in my BD (Mysql / PhpMyAdmin) with another I am giving via an interface, so that both match and an employee number is deleted from the registry currently have that DELETE FROM "+ NAME_TABLA_HEARS +" WHERE numberEmployee = "+ workerCode +" AND date = & # 39; + date + "& # 39 ;; ";…….

On the other hand, I have to do the same, but compare my date stored in the BD with the date of the current day.

Thanks and best regards

sql – problem structuring code permissions (ACL)

I have a problem here to structure the code to verify the permissions.

Let's say I have three actions: read: own_users, read: my_users, read: all_users,

And I have a route / Get users,

I want this route to return all users if the guy doing the request did so read: all_users, But if only he had read: my_users Permission, this route should only return the user who has inserted this type. And the last option if the user has just read: own_users Permission, I want this route to return only your own object.

So what I want to solve is: how do I do that without making a change in my code for each way that puts a place in my SQL?

Or maybe I'm doing it wrong and I have to plan everything to try a new strategy.

SQL Server 2014 – Run Powershell script from administrator as administrator

I need to recycle an AppPool in IIS on my server when the SQL Server service is started.

The path I take is a stored startup procedure that runs an agent job that includes a powershell job step.

I've created a Powershell script that reuses the app pool:

# Load IIS module:
Import module WebAdministration
# Specify a name of the site for which you want to recycle the pool:
$ site = "Default Web Site"
# Determine the pool name based on the site name:
$ pool = (Get-Item "IIS:  Sites  $ site" | Select-Object applicationPool) .applicationPool
# Recycle the application pool:
Restart WebAppPool $ pool

This works at the operating system level, but only if Powershell is running as an administrator (although the account I am logged into Windows with is the same as in the admin group).

Sure enough, if I create the job in Agent with a Powershell step using the above code, I get the error while running

A job step got an error on line 6 in a PowerShell script. The
The corresponding line is & # 39; $ pool = (get-item "IIS: Sites $ site" |)
Select-Object applicationPool) .applicationPool & # 39 ;. Correct the script
and relocate the job. The error information returned by PowerShell
is: & # 39; The dynamic parameters for the cmdlet can not be retrieved. Filename:
redirection.config Error: Configuration file can not be read due to
Insufficient permissions & # 39 ;. Process exit code -1. The step
failed.

So I need a way to run as an administrator from the SQL Server Agent

I succeeded at the operating system level by creating a new script that runs the original script with elevated privileges:

Unless ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]:: GetCurrent ()). IsInRole ([Security.Principal.WindowsBuiltInRole]::Administrator))
{
# Relaunch as an increased process:
Start process powershell.exe "-File", (& # 39; "{0}" & # 39; -f $ MyInvocation.MyCommand.Path) -Verb RunAs
exit
}
# Start now with increased execution, so start the script:
& "d:  path  to  my  script.ps1"

This allows me to run the new script from either a non-elevated CMD prompt or an unresolved Powershell command prompt. However, when I try to run the agent as a powershell or CMD step, I get it

The default permission settings for computers do not grant local activation
Authorization for the COM Server application with CLSID {longguidhere}
and APPID {longguidhere} for the user NT SERVICE SQLSERVERAGENT SID
(longguidhere) from Address LocalHost (Using LRPC) that runs in the
Application Container Unavailable SID. This security
Authorization can be changed using the Component Services component
Tool.

I tried too

Start-Process Powershell -Verb runAs -FilePath "d:  path  to  my  script.ps1"

which, in turn, worked at the OS level, but when run by the agent, gives me the same error.

How can I get that?

a: Recycle the app pool without requiring elevated permissions

or

b: Run the script to recycle the app pool with elevated permissions from the SQL Server Agent.

mysql – Confused with a specific SQL query, AvgPrice

Please let me know if this is correct and if not, how the SQL query would be written correctly or best. I'm a beginner, so I apologize if that's obvious to you. I think I have to use a JOIN, but I do not know how.

This is only a matter of question C) Determine the average price of the products produced by each product line. Name the average price "AvgPrice".

my db layout

        CHOOSE AVG (Price) AS "AvgPrice"
FROM product_line
GROUP BY product_line;

sql – how to make a backup of a button in Visual Studio C #?

You will see that I have a database with the corresponding tables and data. Now, since I was able to perform a backup from a button, I need a stored procedure for that

Create the spCrearBackup procedure
@direc varchar (30)
when
begin
Backup database RHHH on disk = @direc with init
The End
go

but I do not know how to do it via the button, I also want to manually save the location of this copy.

Enter the description of the picture here

In the "Search" button, I will use it to open a copy location. I have this code

                            FolderBrowserDialog obj = new FolderBrowserDialog ();
obj.Description = "Select the route";
obj.RootFolder = Environment.SpecialFolder.Desktop;
if (obj.ShowDialog () == System.Windows.Forms.DialogResult.OK)
{
txtDirecCreate.Text = obj.SelectedPath + "\";
}

As I said, I want to be able to manually create a backup with a location by clicking on the "Create Backup" button. My program is summarized in three shifts, thanks

How do I measure the performance of a single SQL execution in Oracle?

I have to measure the duration of individual SQL executions in Oracle DB.

I have the following query, but it has two main problems.

CHOOSE
Instance name, host name,
sql_id, sql_fulltext, plan_hash_value,
CASE
IF versions> 0
THEN ROUND (expired_time / executions, 3)
ELSE NULL
END elap_per_exec,
elapsed time,
executions
PHYSICAL_READ_BYTES / 1024 read_kb,
buffer_gets,
Zeilen_verarbeitet
FROM v $ sqlarea, user_users
CROSS JOIN (SELECT instance name, hostname FROM v $ instance)
WHERE v $ sqlarea.PARSING_USER_ID = user_users.user_id AND Execution> 5
  1. There is a sense of achievement, but this is the total duration for all executions divided by the number of executions so peak times are not perceived

  2. Duration is the sum of all CPUs that spent time on the task, including parallel tasks. A query that took 1 second may contain 48 cores, so it will appear as 48 seconds.

Is there a way to get the performance for a single run?

Azure SQL Server – large query to select a failed execution plan

Let's just throw this out: If a query is big and confusing, it's probably also big and confusing for the optimizer.

Should I specify the join type and index name for each join to keep the original execution plan? Is that the best way?

No, because these hints may not always be the right choice.

It would probably make more sense to inherit the part of the query for which you get a good plan and copy it to a dump #temp Table.

From there, add your other shortcuts to the temporary table. That way, the optimizer has less bad decisions to make. Sure, you may have to put an index on the temp table, but that's a lot less painful than putting together a bunch of brittle cues.

sql server 2008 r2 – Prevents the last trigger from being triggered unnecessarily

We have an older application running on MS SQL2008R2. For DML operations in some of these older tables, I need to write data to a set of tables in a different database under the same instance. These older tables already have their own INSERT / UPDATE triggers, which I can not change. In addition, the company is unwilling / unable to deploy change tracking / replication technology. So I decided to use additional INSERT & UPDATE triggers for these tables and marked my triggers with sp_settriggerorder as the load. However, the deprecated INSERT trigger updates data in the same table that triggers the deprecated UPDATE triggers and then the trigger for the most recent update. For example, Table1 has insert_trigger and update_trigger, to which I have added my load triggers myLast_insert_trigger and myLast_update_trigger. insert_trigger has an update that inserts UPDATE Table1 c1 = getdate ()
Where insert.primkey = Table1.primkey. This triggers update_trigger, myLast_update_trigger, and finally myLast_insert_trigger in this order. This is expected, but I do not want myLast_update_trigger to fire here. Is there an easy way to achieve this? I thought about interrogating the system tables and trigger_NestLevel within the last triggers to get around the last trigger logic, but that makes my implementation even more complicated.
I would be very happy if there is a mechanism other than Trigger (but not the tracking / replication change :)) that anyone can suggest.