Restore from local disk vs SAN on physical machine (SQL Server Linux)

So I am new to Linux and our client decided to use SQL Server on Linux. As soon as we started testing, we ran into performance issues.

The server in question is a physical machine configured with a SSD local disk and a “natively” attached Storage. I don’t know what a quoted “natively” actually mean here. Linux guy’s exact words below

the storage is “natively” attached to the server

The storage area network is also built on top of SSD and I copied a 4.7 GB backup file from there over to local SSD within a second.

cp gsfssqlbackupstestdb01.bak tmpbackupstestdb01.bak

But when I try to restore from

  1. SAN

RESTORE DATABASE successfully processed 612466 pages in 369.425 seconds (12.952 MB/sec).

  1. Local Disk

RESTORE DATABASE successfully processed 612466 pages in 26.248 seconds (182.295 MB/sec).

The database files are also stored on the SAN and while SAN to physical machine has not connectivity issues, I am certain, the way MS SQL reads the files on SAN has something to do with the slowness.

The database size is not big enough and the database is usually idle based on wait statistics

enter image description here

wait_type   wait_time_s signal_wait_time_s  resource_wait_time_s    pct
SOS_WORK_DISPATCHER 9643760.19  114.83  9643645.35  98.2
PWAIT_EXTENSIBILITY_CLEANUP_TASK    174660.62   174660.62   0   1.78
BACKUPTHREAD    676.2   0   676.19  0.01
BACKUPIO    627.86  0.37    627.49  0.01
PREEMPTIVE_OS_LOOKUPACCOUNTSID  342.43  0   342.43  0
PREEMPTIVE_OS_AUTHENTICATIONOPS 298.48  0   298.48  0
PAGEIOLATCH_SH  116.55  0.04    116.51  0
PREEMPTIVE_OS_QUERYREGISTRY 28.7    0   28.7    0
SERVER_IDLE_CHECK   22  0   22  0
PREEMPTIVE_OS_WRITEFILEGATHER   13.71   0   13.71   0
PARALLEL_REDO_WORKER_WAIT_WORK  9.78    0.01    9.78    0
STARTUP_DEPENDENCY_MANAGER  5.11    0   5.11    0
IO_COMPLETION   5.02    0   5.02    0
PWAIT_ALL_COMPONENTS_INITIALIZED    3.75    0   3.75    0
PREEMPTIVE_OS_FILEOPS   3.41    0   3.41    0

I want to understand why SQL Server is restoring from SAN slower than local file system. I would understand if the difference was little, but currently the different is dramatic. Since our data files and log files are also on SAN, we need to ensure disk is read/written on as fast as possible.

Most of settings are set as default
network packet size is 4096

sql – Seria esse tema bom para TCC?

Será que esse título seria bom para um TCC verificação da diferença de desempenho entre Banco de dados relacionais e NoSQL

SQL change record

What statement can I use to show what records have changed?
For example, can I run a query that shows when Rocco, eventually, logged in from Ireland, as opposed to logging in from Italy?

enter image description here

sql server – Bypassing version store for changing column datatype

Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with “alter table” command) on a column on a large (1 TB) table.
It blew up our tempdb due to version store.
I understand why this happened, but really just looking at (maybe creative) ways to stop this happening (ideally to bypass the version store for this command).
I’m considering recommending ADR to at least mean that at least issue is isolated to this particular database, but would love to hear any other ideas.

SQL server XML query with xquery

I’m trying to reach “row” data below using xquery on sql server.

DECLARE @xmlData XML
SET @xmlData = '
<Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <WorkContext xmlns="http://oracle.com/weblogic/soap/workarea/">rO0ABXdbABh3ZWJsb2duNDAAAA==</WorkContext>
  </Header>
  <Body>
    <getReport xmlns:ns0="http://report_xml.org">
      <return>
        <pagekey>F1D96FB7ACFBA3BD5D5E77C5</pagekey>
        <rapor>
          <dataSet xmlns="http://report_xml.org/dataSet/201006">
            <dataTable>
              <id>ABC1</id>
              <row>
                <dim1>Paris</dim1>
                <dim2>Societe</dim2>
                <dim3>Car</dim3>
              </row>
              <row>
                <dim1>Paris</dim1>
                <dim2>Credit</dim2>
                <dim3>Car</dim3>
              </row>
            </dataTable>
          </dataSet>
        </rapor>
      </return>
    </getReport>
  </Body>
</Envelope>
'

When I use below code I can get the the “pagekey” value

; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
select @xmlData.query('/Envelope/Body/getReport/return/pagekey')

or I can reach the “dataSet” xml using this one

; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet')

However, when I try to reach further down like

; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet/dataTable/id')
or
select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet/dataTable/row')

Queries return empty.

what am I missing?

sql server – What will be the relation between Employees and Players, given that some players are also Employees?

I have a requirement where I need to store information on Employees in a company that owns(?) a sports team and also store information on Players from other teams that are not Employees of this company. The Players in the team(i.e owned by the company) are also considered Employees hired by the company.

For the design, what I have done is create two Entities called Employees and Players and have a Player id key as FK in the Employees Entity. The Employee Entity also has Employees that are just employees and not players. With the player id fk, Employees that are Players will have a player id fk, while other employees will not. That way I have information about Employees, player or just normal employees, in Employee entity. And in the Players entity I only have information specific to all the players hired by the company or just other players from other teams(not owned by the company). Is this the right way to go? I haven’t shown the subclass/subtype relation between Employees and Players entity because not every player in the Players entity is an Employee. Am I in the right direction?

I know this is not the very best explanation I guess but please do ask for any clarification. Thank you

sql server – SP2007 (All)UserData nvarchar field content garbled for choice fields

In our Sharepoint 2007, one of our lists field is configured as such:

<Field Type="Choice" DisplayName="Standard" Required="FALSE" Format="RadioButtons" FillInChoice="FALSE" Group="gc_xyz" ID="{e5d39160-a777-4d70-b372-a7ca76305adc}" SourceID="{21f217b9-cbc5-44b8-96b7-2c665aecc37f}" StaticName="Standard" Name="Standard" ColName="nvarchar20" RowOrdinal="0"> <CHOICES> <CHOICE>Yes</CHOICE> <CHOICE>No</CHOICE> </CHOICES> </Field>

But when I look in the AllUserData table (or its view), the data for this field is like this:
| nvarchar20 |
|————|
|챂䗅⎑啄獌崿|
|ំ싖줚䎭권䞢⋫|
|嫎⚔潣俎즤떴ಇ긅|
|စ꼨噡䊔ꆫ䐂㪗⋉|
|ᶷ刊ᯉ䯥梋蓊㯕Ꙃ|
|㩝䪿撾럌阶紻|
|ពု帵䙏熦༱䏇䶌|
|왜汵䅩粁ʹ猅|

All values are different, as if hashed. How do I read those values to translate them to Yes/No?

sql – Se pueden utilizar 2 tipos de bases de datos diferentes en el mismo programa?

Quiero hacer un proyecto y he estado averiguando cual base de datos es mas conveniente, tengo entendido que para el login es mejor una base estructurada (sql) y para subidas de archivos es mejor no estructurada (no-sql). Mi pregunta es si puedo usar por ejemplo MySQL y MongoDB en un mismo programa. Disculpen mi ignorancia, soy un poco nuevo en esto 🙂

SQL 2016 – NU/NC Index Missing From sys.partitions

Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view? There is a CI on the same table and there is a record for it in the view. My understanding is that any NCI should have at least one record in sys.partitions. Thanks.

What is a merge log in SQL? How is it structured, and what are its uses?

While creating merges, I was told of something called a merge log. I have not heard of this before. What is a merge log in SQL? How is it structured, and what are its uses? I appreciate any responses. Thank you in advance.